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

[Scott Robison]

They can subscribe to the forum too. :)

On Thu, Mar 12, 2020, 2:40 PM Simon Slavin  wrote:

> Well, that'll annoy the nabble people.  And I can live with that.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Scott Robison]

On Tue, Feb 4, 2020, 5:23 PM J. King  wrote

> Not everyone has access to carrays and intarrays, either, such as PHP
> users like myself.
>

But everyone has access to temp tables, and I think the idea of creating a
temp table, inserting 1000 items in a loop, and using that temp table in
the query, is a lot more clear than formatting a query as a string with
?1000 or more bound parameters. It is more idiomatic SQL, I would think.


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

Re: [sqlite] importing data to a table that has generated-columns

[Scott Robison]

On Sun, Jan 26, 2020 at 11:01 AM chiahui chen 
wrote:

> Hi,
>
> After creating a table (total 8 columns including 1 generated column) , I
> tried to import data from a csv file (each record has values for 7 columns
> that match the non-generated column names and data types, no headers ).
>
> The system issued " error:  table has 7 columns but 8 values were supplied.
> ' I wonder why.
>
> After experimenting different ways to import data to a table that has a
> generated column, so far I only found that  .read command with a .sql file
> that was output  as a result of  'mode insert'  is successful. Is there any
> other ways to import data to an existing generated-column table?
>

I would be inclined to import the csv as a temp table, then write an INSERT
INTO ... SELECT ... query to move the appropriate columns from the temp
table into the new table.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Coding standard

[Scott Robison]

On Thu, Dec 12, 2019, 11:04 PM Valentin Davydov 
wrote:

> On Thu, Dec 12, 2019 at 11:19:44AM -0500, Richard Hipp wrote:
> >
> > #define sqlite3Strlen30NN(C) (strlen(C)&0x3fff)
> >
> > The tool does not provide any details beyond "Use of strlen".
>
> So why not just #define sqlite3Strlen30NN(C) (strnlen(C,0x3fff))
>

strnlen is not in the standard library. It is available on many platforms
but is not standard.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] last occurrence of /*

[Scott Robison]

On Fri, Dec 6, 2019, 4:31 PM Bart Smissaert 
wrote:

> I know I can do something like this:
>
> select replace(postcode, rtrim(postcode, replace(postcode, ' ', '')), '')
> from addresses
>
> which will get the part of the postcode starting with the space.
> Problem however is how to deal with the forward slash.
>

Do you have to do this in SQLite itself? Can you load an extension that
provides regex? How did you go from comment strings to postcodes?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] last occurrence of /*

[Scott Robison]

On Fri, Dec 6, 2019, 4:00 PM Bart Smissaert 
wrote:

> Have table with SQL statements and these statements may have comments,
> starting with /*
> How do I select the part of this statement starting with the last /*  ?
> So if the statement is:
> select field1 /*comment 1 */ from table1 /*comment 2*/
> I would like to get:
> /*comment 2*/


Is there more format to the comment than just plain text?

My senior project was a SQLite helper that would read a bunch of statements
from a sql script and create classes to automate the boilerplate code and
ensure they were prepareable. To accomplish this I needed some new syntax
that wouldn't interfere with SQLite, so I embedded it in comments, but they
had to conform to a simple structure so that I could find them via regex.

Perhaps, if it is only the last comment you care about, you could use a
regex. I'm on a phone right now and don't want to try to type in valid
regex strings, but that would be useful I could help with it when I'm near
a real keyboard.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is the C language standard to which sqlite conforms ?

[Scott Robison]

On Mon, Nov 18, 2019 at 3:44 PM Dennis Clarke  wrote:

>
> Same question as a few days ago.
>
> This may have been asked many times before but always seems to be a
> valid question.  On some machines with different compilers I get good
> results using C99 strict compliance. On other machines, such as those
> running Red Hat Enterprise Linux, I get terrible results.
>

Per https://www.sqlite.org/howtocompile.html it is "ANSI-C". C89 is the
ANSI-C standard, C90 is the first ISO-C standard. They are practically
identical.

Note that it is not strict ANSI-C, since ANSI-C doesn't provide for 64 bit
integers, and it does not provide for platform specific APIs or functions.
But as much as is possible, it is written to work with standard C as it has
existed for about 30 years now.

Different compilers have various degrees of compliance with their C89 / C90
/ C99 implementations. C99 is more strict about some things that C89 did
not care about, and the developers have made concessions on occasion to
conform to C99 when it did not compromise C89 compatibility. But as much as
possible it is desired to maintain C89 compatibility because there are
platforms that still are stuck with older compiler standards.

Note: I am not a member of the dev team, just recounting my understanding
of how things are.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Scott Robison]

On Thu, Jun 13, 2019, 8:51 AM R Smith  wrote:

> On 2019/06/13 4:44 PM, Doug Currie wrote:
> >>
> >> Except by the rules of IEEE (as I understand them)
> >>
> >> -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero"
> >>
> > Except that 0.0 is also an approximation to zero, not "true zero."
> >
> > Consider that 1/-0.0 is -inf whereas 1/0.0 is +int
>
>
> I do not know if this is the result case in any of the programming
> languages, but in Mathematical terms that is just not true.
>
> 1/0.0 --> Undefined, doesn't exist, cannot be computed, Should error
> out. Anything returning +Inf or -Inf is plain wrong.
> I posit the same holds true for 1/-0.0
>

One of the features of the standard is "gradual underflow". I think of it
as a limit like concept. Hence why the storage of negative zero has a
place, why the computation of x/+-0.0 yields +-inf. It is more than just an
artifact of the standard intended to solve certain computational problems.

All of that is irrelevant, though. The standard works the way the standard
works. SQLite has the option of either rendering -0.0 with or without a
sign. Should the system be changed?

I think that the possibility of breaking existing SQLite usage by changing
the long established text output makes it ill advised to make such a
change. Combined with the idea that sometimes SQLite will convert between
fp & integer zero on storage, thus losing the sign, illustrates that there
is too much history of how SQLite handles numbers already.

Such a change would not harm me in any way I can perceive. I have not
written any code that depends on -0.0, nor have I written code that depends
on the textual conversion thereof. I just think that SQLite's history of
maintaining backward compatibility by not gratuitously changing established
functionality that some people might depend on, even when it seems like an
improvement, should result in no change in the cast of -0.0 to text.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Scott Robison]

On Wed, Jun 12, 2019, 10:02 AM James K. Lowden 
wrote:

> On Wed, 12 Jun 2019 09:35:13 -0400
> Richard Hipp  wrote:
>
> > Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?
>
> No.
>
> 1.  Prior art.  I can't think of a single programming language that
> displays -0.0 without jumping through hoops.
>
> 2.  Math.  Negative zero is not a mathematical concept.
>
> 3.  Utility.  There is none.  The user is only inconvenienced.
>
> Negative zero is an artifact of the IEEE 754, not a feature.  By
> contrast, IEEE adopted 2s-complement signed integers specificially to
> avoid negative zero.  That's evidently harder to do with floating
> point. ;-)
>
> I see no advantage to exposing a technical mechanical artifact to the
> user.
>

Further, how much code might exist that depends on the current
functionality? Code that converts a real to a string then compares the
string value against 0.0.

If the decision is made to differentiate 0.0 with a prefixed sign, will all
positive values be likewise prefixed with + explicitly when converted to
string?

I think leaving it with the status quo is the best course of action.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with sqlite3_value_text

[Scott Robison]

On Fri, Apr 12, 2019, 1:06 PM Keith Medcalf  wrote:

>
> Actually you would have to convert the strings to UCS-4.  UTF-16 is a
> variable-length encoding.  An actual "unicode character" is (at this
> present moment in time, though perhaps not tomorrow) 4 bytes (64-bits).
>

That is some impressive compression! :)

Regardless, even if you use UCS-4, you still have the issue of combining
characters. Unicode is complex as had been observed.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Variable Declaration

[Scott Robison]

On Sat, Jan 19, 2019, 6:53 AM Simon Slavin 
> On 19 Jan 2019, at 4:49am, Stephen Chrzanowski 
wrote:
>
> > I know about the bindings.  I don't know about all languages supporting
it.
>
> Bindings are part of the SQLite API.  Any language which can make SQLite
calls should be supporting binding.
>
> Using binding means you can have the variables you want -- as many as you
want -- in whatever programming language you're using.  SQLite doesn't need
variables before your programming language has variables.  This is why
SQLite doesn't need variables.


Unless you want to write a script to run from the sqlite3 shell. Then you
don't have access to "variables" in the way the OP suggested.

Of course, you can use temp tables as though they are variables, though the
syntax isn't as elegant as the provided example.

1> declare @count int = 10;
2> select * from test where i<=@count

can be reformulated as

1> create temp table vars(name, value);
2> insert into vars values('count', 10);
3> select * from test where i <= (select value from vars where
name='count');

That's just one example, of course. Multiple variations on the theme are
possible if one needs variables that live exclusively in SQL without using
a host language and that also survive for more than a single statement.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Scott Robison]

On Mon, Dec 17, 2018 at 2:50 PM Thomas Kurz  wrote:

> Ok, as there seem to be some experts about floating-point numbers here,
> there is one aspect that I never understood:
>
> floats are stored as a fractional part, which is binary encoded, and an
> integer-type exponent. The first leads to the famous rounding errors as
> there is no exact representation of most fractions.
>
> Can someone explain to me why it has been defined this way? Having 1 bit
> sign, 11 bit exponent, and 52 bit mantissa, I would have stored the (in the
> meantime well known) number 211496.26 as 21149626E-2, i.e. I would have
> stored a 52 bit integer number and appropriate exponent. This way there
> should be no rounding errors and one would always have a guaranteed
> precision of ~15 significant digits.
>

To get the maximum precision possible from a binary floating point number,
the designers of the format took advantage of the fact that all numbers
other than zero would have a 1 bit set somewhere in their representation.
To that end, "normal" floating point numbers actually have a 53 bit
mantissa. "But that equals 65 bits! You can't cram 65 bits into a 64 bit
word." But you can if the most significant set bit of the mantissa is
implied just to the left of the explicitly given 52 bits of the mantissa.
The most significant digit of a decimal number can be any value from 1
through 9, so you can't use this same trick to extend the precision of a
decimal floating point number.

In addition to normal numbers, there are subnormal numbers, where the left
most digit is implicitly a 0 bit. The value zero happens to be a subnormal
number with all bits set to zero.

Even without the implicit bit, many / most schemes for encoding decimal
digits in binary lose some portion of the range that is possible with
binary representations, and the IEEE designers wanted the best of both
worlds, range and precision, so they gave up exact decimal representation
in favor of binary.

Your approach of coding is what the decimal type does in the .net platform,
among other examples, but the available range is smaller than IEEE binary
floating point numbers of the same size.

As far as it goes, you can still have rounding errors that propagate with a
decimal scheme such as you suggest. Simply add 1/3 + 1/3 + 1/3 in a decimal
representation.

333E-15 + 333E-15 + 333E-15 =
999E-15. But it should be 1000E-15 (or 1E0). It
doesn't matter how many bits of precision you add, you can never do this
type of math exactly with decimal floating point numbers. Any time the
decimal expansion extends beyond the bit length of the available precision,
rounding choices are going to have to be made at some point, and some
calculation will be inexact.

Note: I am spouting from memory, so my apologies if I've gotten any
terminology wrong (such as subnormal vs denormal, so similar other ideas).

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

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[Scott Robison]

On Tue, Oct 9, 2018, 6:34 AM Will Parsons  wrote:

> On Sunday,  7 Oct 2018  5:25 PM -0400, Keith Medcalf wrote:
> >
> > Many people do not "do" web forums.  I am one of them.  If there is not
> a mailing list then it does not exist.
>
> I completely agree.  I read and post to the SQLite mailing via Gmane,
> and I used to do the same for Fossil.  Now that the Fossil mailing
> list has gone away, I can't use Gmane any more, so I no longer follow
> Fossil.  Please, let's *not* have the same thing happen with SQLite3!


Isn't Gmane a web forum style interface to email lists? That doesn't seem
much different than what fossil is now doing, though maybe my lack of
experience using Gmane is preventing me from seeing a difference.

As described by at least one other, I use email to track the fossil forum
just as I always have, and can log in there to post when I want. I can
appreciate why people who refuse to use a web interface might object to it,
but not Gmane.

There are potential cons to the forum, but there are pros as well.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why sqlite fts5 Unicode61 Tokenizer does not support CJK(Chinese Japanese Krean)?

[Scott Robison]

On Fri, Sep 21, 2018 at 12:39 AM 邱朗  wrote:
>
> >I think it could be made to work, or at least, I have experience
> >making it work with CJK based on functionality exposed via ICU. I
> >don't know if the unicode tokenizer uses ICU or if the functionality
> >in ICU that I used is available in the unicode tables. Not
> >understanding any of the languages represented by CJK, I can't say
> >with any confidence how good my solution was, but it seemed to be good
> >enough for the use case of my management and customers in the impacted
> >regions.
>
> I am Chinese and I know a little bit of Korean, I can help to test your 
> product :D  All Jokes aside I also tried to build an ICU SQlite macOS version 
> but I failed. All the document I googled seem outdated. e.g. I used this (and 
> other solutions) but I just can not build a macOS version. Do you have any 
> experience for that ?
>
>
> ./configure CFLAGS="-I/usr/local/opt/icu4c/include -DSQLITE_ENABLE_ICU 
> `icu-config --cppflags`" LDFLAGS="-L/usr/local/opt/icu4c/lib `icu-config 
> --ldflags`"

I do not have macOS experience, and I don't have a lot of free time
either. Mainly I was trying to explain what I had done in the past and
what I used. Hopefully someone else will chime in with more details
about the Unicode61 tokenizer and whether it is standalone or depends
on ICU.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why sqlite fts5 Unicode61 Tokenizer does not support CJK(Chinese Japanese Krean)?

[Scott Robison]

On Fri, Sep 21, 2018 at 12:02 AM 邱朗  wrote:
>
> https://www.sqlite.org/fts5.html said " The unicode tokenizer classifies all 
> unicode characters as either "separator" or "token" characters. By default 
> all space and punctuation characters, as defined by Unicode 6.1, are 
> considered separators, and all other characters as token characters... "  I 
> really doubt unicode tokenizer requires white space, that is ascii tokenizer.

Forgive my imprecise use of language. I should have said separators
instead of whitespace. Regardless, CJK uses implicit separation
between words, and that description seems to indicate that the unicode
tokenizer expects explicit separators (be they whitespace or
punctuation or something else) between tokens.

> That was why I thought it might work for CJK.

I think it could be made to work, or at least, I have experience
making it work with CJK based on functionality exposed via ICU. I
don't know if the unicode tokenizer uses ICU or if the functionality
in ICU that I used is available in the unicode tables. Not
understanding any of the languages represented by CJK, I can't say
with any confidence how good my solution was, but it seemed to be good
enough for the use case of my management and customers in the impacted
regions.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why sqlite fts5 Unicode61 Tokenizer does not support CJK(Chinese Japanese Krean)?

[Scott Robison]

On Thu, Sep 20, 2018, 8:21 PM 邱朗  wrote:

> Hi,
> I had thought Unicode61 Tokenizer can support CJK -- Chinese Japanese
> Korean I verify my sqlite supports fts5
>
> {snipped}
>
> But to my surprise it can't find any CJK word at all. Why is that ?


Based on my experience with such things, I suspect that the tokenizer
requires whitespace between adjacent words, which is not the case with CJK.
Word breaks are implicit, not explicit.

Is the Unicode61 tokenizer based on ICU? I had to implement an algorithm
for software at work that used functionality from ICU to find CJK word
boundaries, so I believe it is possible, just not as straightforward as
whitespace delimited words.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign_keys = 0N with Entity Framework 6.2.0

[Scott Robison]

On Thu, Sep 13, 2018 at 2:05 AM Urs Wagner  wrote:
>
> SQLite 3.24.0
> Entity Framework 6.2.0
>
> The result cannot be coerced into a string (compiler error).

Maybe I'm misreading this, but the subject line shows "foreign_keys =
[zero][en]" ... it appears the word ON ([oh][en]) is being written as
0N ([zero][en]) instead.

Maybe that's just an email typo, but thought I'd point it out.

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

Re: [sqlite] Lemon Parser vs bubble-generator.tcl

[Scott Robison]

On Fri, Aug 31, 2018 at 2:59 PM Warren Young  wrote:
>
> On Aug 31, 2018, at 1:55 PM, Scott Robison  wrote:
> >
> > Is one generated from the other, or are they maintained separately?
>
> They’re separate.  Here’s the Tcl source for the bubble diagrams:

As I suspected having looked at them in the source tree. Thanks for
the confirmation.

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

[sqlite] Lemon Parser vs bubble-generator.tcl

[Scott Robison]

SQLite language as implemented uses a Lemon based parser. The syntax
diagrams are created from the bubble-generator.tcl script. Is one
generated from the other, or are they maintained separately? Is one
(or another file that I don't know exists yet) the canonical
description of the "complete" SQLite syntax?

I ask because I'm working on a project idea for a senior project class
I'll be taking this fall and want to do something related to
identifying SQL syntax at C++ compile time. In doing a little research
/ planning, I started by creating my own syntax description from some
of the diagrams, then realized "I should just use portions of the
syntax diagram script directly", then started wondering about the
parser vs the diagram script.

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

Re: [sqlite] Best practices for forward conversion of database formats?

[Scott Robison]

On Thu, Aug 30, 2018 at 11:10 AM Roger Binns  wrote:
>
> On 30/08/18 09:51, Randall Smith wrote:
> > is how to convert existing data from one DB format to another, given some 
> > arbitrary set of changes in the database schema in the interim.
>
> I use SQLite's user pragma.  It starts at zero.
>
> https://sqlite.org/pragma.html#pragma_user_version
>
> My code ends up looking like this:
>
> if user_version==0:
> CREATE TABLE IF NOT EXISTS events(key, time, message);
> PRAGMA user_version=1;
>
> if user_version==1:
> CREATE INDEX IF NOT EXISTS [events:message] ON events(message);
> PRAGMA user_version=2;
>
> if user_version==2:
> ALTER TABLE events ADD COLUMN severity;
> PRAGMA user_version=3;
>
> This ensures that the currently running code will upgrade the schema as
> needed.  Ensure the commands are wrapped in a transaction so they either
> completely happen or not.
>
> I am helped by having low complexity schemas.  If yours are large you
> could probably generate something like the above.  Some ORM style
> engines also have schema and data upgrade functionality.

One place I worked for had a proprietary ORM style interface that was
intended to bridge the gap between an older legacy database and
SQLite. It's upgrade process involved an in memory data structure that
listed each upgrade step (add a column, move data, drop a column,
create a table, etc) then for each step, read each row of data, write
a custom insert statement, prepare, execute, finalize, discard, lather
rinse repeat. It was very slow. Very very slow. I don't think it is
possible to qualify it with sufficient verys to convey just how slow
it was.

I replaced it with a straight forward implementation that did a direct
SQL statement migration from the old schema to the new schema. Begin a
transaction, one statement per table, commit the transaction. We wound
up going from as much as 48 hours to migrate a 10 GB or so DB to about
5 to 15 minutes (it's been a while, I don't remember exactly).

If you have a simple schema and/or a small data set, the ORM migration
approach might work well for you. If you have a complex schema and
especially if you have a large data set, I'd encourage you to look
into alternatives to an ORM approach. I don't think all ORMs would
necessarily be as slow as the one I worked with was, but it was just
the wrong tool for the job in that particular case.

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

Re: [sqlite] Strange Corruption Issue

[Scott Robison]

On Mon, Jun 18, 2018 at 9:15 PM, Patrick Herbst  wrote:
> I'm using sqlite in an embedded application, running on SSD.
>
> journal_mode=persist
> so that it is more resilient to loss of power.
>
> I'm seeing corruption.  I'm using sqlite to log events on the system,
> and the corruption is well in the middle of a power session; not at
> the tail end of log when a power loss might occur.
>
> What i'm seeing is just a few pages corrupted with random bits being
> flipped.  looking in a hex editor I can see the corrupted data, and
> where I can tell what values it SHOULD be, I see that they're wrong,
> but only by a single bit flip in random bytes here and there.  for
> example a "A" is "a", or a "E" is "A".  These are all changes of a
> single bit.  there are far more examples... but in pretty much every
> case (even when RowID's are wrong) its just off by a bit.
>
> I'm using sqlite 3.7 (i know, old, but this this system is old).  Has
> anyone else seen random bit flips?  Any idea what could be causing it?

My first guess would be failing RAM chips.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Idea: defining table-valued functions directly in SQL

[Scott Robison]

On Sat, Jun 9, 2018 at 7:00 PM, Andy Goth  wrote:
> On 06/09/18 18:04, Simon Slavin wrote:
>>
>> CREATE TABLE tempLog (
>> datestamp TEXT COLLATE NOCASE PRIMARY KEY,
>> centTemp REAL,
>> fahrTemp AS (centTemp*9/5 + 32) )
>>
>> I'm happy with another syntax as long as it does the same thing.
>
>
> CREATE TABLE tempLog (
>datestamp TEXT COLLATE NOCASE PRIMARY KEY
>  , centTemp  REAL);
> CREATE VIEW tempLogView AS
>  SELECT *
>   , centTemp * 9 / 5 + 32 AS fahrTemp
>FROM tempLog;
>
>> Niggle 1: Can a computed column refer to a column defined after it ?
>
>
> With the view syntax I showed above, "computed" columns can only refer
> to columns that exist in the underlying tables.  I wish SELECT statement
> expressions could refer not only to input columns but also output
> columns that have been named using AS, but we don't have this feature.

SELECT *, (computation on Y) AS X FROM (
   SELECT *, (some computation) AS Y FROM sometable)

It is a little annoying having to nest them, but it works.

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

Re: [sqlite] sqlite.org website is now HTTPS-only

[Scott Robison]

On Fri, Jun 8, 2018, 12:19 AM Ron Yorston  wrote:

> Dennis Clarke wrote:
> >On 6/7/18 9:59 PM, Richard Hipp wrote:
> >> On 6/7/18, Scott Doctor  wrote:
> >>> Just out of curiosity, is the sqlite website using nginx or
> >>> apache as the server?
> >>
> >> None of the above.
> >>
> >> The web server is one that I wrote myself
> >
> >You're level of cool just jumped to UNIX silverback level :-)
>
> Meh.  *All* programmers of a certain age wrote their own web server.
>

But did they write it with their own text editor? :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Feature suggestion / requesst

[Scott Robison]

On Fri, Jun 8, 2018, 12:11 AM Hick Gunter  wrote:

> >
> >
> >I've encountered a feature that I think would be awesome:
> >https://www.postgresql.org/docs/9.3/static/dml-returning.html
> >
> >Example: INSERT INTO blah (this, that, another) VALUES (x, y, z)
> RETURNING id;
> >
>
> What does this do if the INSERT creates multiple rows? What about inserts
> generated from trigger programs?


Excellent questions that I don't know the answers to, but this does not
stop me from having an opinion.

I think if multiple rows are inserted, this should return multiple rows. It
is effectively a select of the inserted data.

Triggers are external to the insert, so I would expect them to not
contribute to the returning syntax result set.

Ultimately I expect, if accepted as an enhancement to SQLite, the team
would ask WWPGD, and model the implementation on that. I am not an expert
at PG so my ideas above could be way off base. I would think DML statements
using returning could be used anywhere a select could be used making for
more expressive SQL without necessarily needing to drop to the host
language.

Just a thought that I found interesting.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature suggestion / requesst

[Scott Robison]

On Thu, Jun 7, 2018, 9:25 PM Rowan Worth  wrote:

> On 3 June 2018 at 07:28, Scott Robison  wrote:
>
> > I've encountered a feature that I think would be awesome:
> > https://www.postgresql.org/docs/9.3/static/dml-returning.html
> >
> > Example: INSERT INTO blah (this, that, another) VALUES (x, y, z)
> RETURNING
> > id;
> >
>
>
> > my thoughts are just that this could greatly simplify a lot of sql
> > code that currently has to prepare and execute at least two statements
> > to accomplish what is conceptually an atomic task.
> >
>
> For most use cases you only need a single query:
>
> if (sqlite3_exec(db, "INSERT INTO blah (this, that, another) VALUES (x,
> y, z)") == SQLITE_OK) {
> long id = sqlite3_last_insert_rowid(db);
> ...
> }


Fair enough. My statement was intended as a SQL only solution that is
independent of the language bindings in use.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Filename encoding on Unix platforms

[Scott Robison]

On Mon, Jun 4, 2018 at 10:56 PM, Christopher Head  wrote:
> Hello,
> I have a question regarding text encoding of filenames on Unix
> platforms. I’ve read the two related mailing list threads I could find
> in the archive,
> 
> and
> .
> Both of those explain that, on Unix platforms, the filename string is
> passed unmodified by SQLite directly to the open() syscall.

{snipped stuff}

While I cannot comment on which of 1 through 3 is true, given that the
VFS layer is adaptable, it would be "simple" (if so desired) to adapt
SQLite to do whatever filename transformation was required for a
particular system. You likely have thought of this (and hopefully I
didn't miss it in your post), but there is no reason why SQLite
couldn't support both Glib and Qt (and even other) file open semantics
even if the core team opted not to support them within the Unix VFS
code itself.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Feature suggestion / requesst

[Scott Robison]

Background: I never finished a degree back in the dark ages, but
recently was provided an opportunity to earn a degree to go along with
my experience at a really affordable price. As a result, I'm taking
various classes to demonstrate my worthiness. :)

Last semester I had a class that used Oracle. I still have nightmares.

This semester I am taking a class that has exposed me to postgresql
for the first time. I can appreciate why the SQLite teams question is
WWPGD when considering new features.

I've encountered a feature that I think would be awesome:
https://www.postgresql.org/docs/9.3/static/dml-returning.html

Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING id;

It seems a very handy "single step" way (from the perspective of the
SQL programmer) to "select" some data from insert / update / delete
statements.

I concede to anyone who thinks this isn't very light their argument,
and agree it would add some amount of heft to SQLite. I suspect not
much, but I also know how easy it is for people who have no idea to
say that to me about my own software. I don't know. I concede to
anyone else who thinks we already have ways to do this their argument,
my thoughts are just that this could greatly simplify a lot of sql
code that currently has to prepare and execute at least two statements
to accomplish what is conceptually an atomic task.

Thank you for your time.

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

Re: [sqlite] This is driving me nuts

[Scott Robison]

Sqlite will use different strategies for ASC and desc ordering and result
set sizes. Perhaps one is creating a temp btree to order the results. I
think explain query plan might help show exactly what sqlite is
contributing to the memory consumption without the need for as much
speculation. Not intended as a critical comment, just a thought in passing.

On Thu, May 31, 2018, 9:22 AM x  wrote:

> Yes, I think you’re right. I suppose maybe it recognises the desc sequence
> from page access.
>
>
>
> 
> From: sqlite-users  on
> behalf of Andy Ling 
> Sent: Thursday, May 31, 2018 3:57:27 PM
> To: 'SQLite mailing list'
> Subject: Re: [sqlite] This is driving me nuts
>
> > While that makes sense Clemens it again fails to explain why it does no
> > caching when desc is stepped through first. I am at least satisfied that
> it’s not
> > something I should dwell on or, more the point, it isn’t down to a bug
> in my
> > code which is why I got embroiled in it in the first place.
> >
>
> My understanding from what Clemens said, which might clarify.
>
> When ascending pages are read sequentially, so sequential mode is used and
> the pages get saved in the cache, but when descending, pages are read in
> reverse order which makes the cache think it's random so it doesn't keep
> them.
>
> So if you do ascending first the cache gets filled and eats up memory.
> Then when you do descending it gradually deletes all the pages from the
> cache
> and frees up the memory. If you do it the other way round, descending
> uses one page worth of memory then ascending eats up enough for all the
> pages.
>
> Andy Ling
>
> ---
> This email has been scanned for email related threats and delivered safely
> by Mimecast.
> For more information please visit http://www.mimecast.com
>
> ---
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite is a LoC Preferred Format for datasets

[Scott Robison]

On Wed, May 30, 2018 at 12:15 PM, dmp  wrote:
> DROP TABLE IF EXISTS mySinkDBTable;
> CREATE TABLE mySinkDBTable (
> key_id1 INTEGER UNSIGNED NOT NULL,
> key_id2 INTEGER UNSIGNED NOT NULL,
> text VARCHAR
> );
>
> --
> -- Dumping data for table mySinkDBTable
> --
>
> INSERT INTO mySinkDBTable (key_id1, key_id2, text) VALUES('1', '8', '51');
> Corrected:
> INSERT INTO mySinkDBTable (key_id1, key_id2, text) VALUES(1, 8, '51');
>
> Since the user is allowed to store the metadata for the table
> types, example above, it is difficult for tools too determine
> the proper processing for the data. I understand the flexibility,
> and perhaps typeof() would solve most of my issues, but it would
> be nice to have metadata field type stored as INTEGER, REAL,
> NONE, TEXT, or BLOB.

What version of SQLite are you using for this? I just did the
following and do not see the string quoted values you are describing:

sqlite> CREATE TABLE mySinkDBTable (
   ...>   key_id1 INTEGER UNSIGNED NOT NULL,
   ...>   key_id2 INTEGER UNSIGNED NOT NULL,
   ...>   text VARCHAR
   ...> );
sqlite> insert into mySinkDBTable (key_id1, key_id2, text) VALUES('1','8','51');
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mySinkDBTable (
  key_id1 INTEGER UNSIGNED NOT NULL,
  key_id2 INTEGER UNSIGNED NOT NULL,
  text VARCHAR
);
INSERT INTO mySinkDBTable VALUES(1,8,'51');
COMMIT;

Given the comment in your data dump, I'm thinking your example came
from MySQL, not SQLite. Even if you try to insert quoted strings into
SQLite with the given column definitions, SQLite converts them to the
given type affinity before storing them, and uses that type affinity
when dumping the database.

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

Re: [sqlite] Always call a value-quoting routine

[Scott Robison]

On Sun, May 6, 2018 at 11:34 PM, Rowan Worth  wrote:
> Amusing -- but without the leading single-quote it would take intentional
> effort for a programmer to detonate this payload.
>
> Its omission is interesting though. Does it indicate an incompetent
> attacker, or is companieshouse.gov.uk using some bespoke approach like
> "delete all single quotes" instead of actually quoting strings?

It could just indicate someone with a sense of humor who crafted a
name that looks like an injection attack for their company.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Always call a value-quoting routine

[Scott Robison]

Thanks for sharing that. It will undoubtedly be useful to me in a computer
security class I'm taking this semester.

On Sat, May 5, 2018, 4:57 PM Simon Slavin  wrote:

> This is a genuine company registered under the UK Companies Act:
>
> 
>
> The name of company is
>
> ; DROP TABLE "COMPANIES";-- LTD
>
> (Note: For legal reasons a UK company name must end in 'LTD' or 'plc',
> depending on the type of company it is.)
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fossil Feature Request

[Scott Robison]

On Fri, Mar 30, 2018 at 1:32 PM, J Decker  wrote:
>  Sqlite's Fossile browser can't link line numbers...
>
>
> Add ability to link to lines of source...
>
>
> was trying to share this as another reference for getting UTF8 characters
> from strings
>
> #define READ_UTF8(zIn, zTerm, c)
> https://www.sqlite.org/src/artifact/810fbfebe12359f1
>
> which is like line 155 and kinda hard to find... when sharing refernces
> from github I can select a line to share directly.

Try something like this:

http://www.fossil-scm.org/fossil/artifact/6eb26bb7a6?ln=1755-1759
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump command and user_version

[Scott Robison]

On Thu, Mar 29, 2018, 8:18 AM Richard Hipp <d...@sqlite.org> wrote:

> On 3/29/18, Scott Robison <sc...@casaderobison.com> wrote:
> >  It seems a
> > reasonable to suggestion to add it.
>
> Version 3.23.0 is in bug-fix-only mode.  It'll have to wait.
>

Of course. I wasn't suggesting that it should be done with all haste, just
agreeing with the original suggestion.

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

Re: [sqlite] .dump command and user_version

[Scott Robison]

On Thu, Mar 29, 2018 at 6:56 AM, Simon Slavin  wrote:
>
>
> On 29 Mar 2018, at 1:47pm, Wout Mertens  wrote:
>
>> I noticed that `.dump` does not output the user_version pragma. It seems to
>> me that that is part of the database data?
>>
>> I don't actually use it, but it might be interesting to add this for
>> completeness?
>
> .dump is for things which affect data and data integrity only.  "PRAGMA 
> foreign_keys" is one of the few PRAGMAs which affects data integrity: if a 
> database is operated with that PRAGMA not set, the data could be corrupted.
>
> Numerous PRAGMAs have no effect on integrity.  For example the journal mode 
> and page size.  They are not included in the output of .dump.

Yet as he said, user_version is data. It can be queried and can be
checked to make decisions about what to do with the data. It seems a
reasonable to suggestion to add it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Scott Robison]

0

On Mar 16, 2018 9:37 AM, "Richard Hipp"  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Scott Robison]

Integer primary key is by definition not null, so looking for a null value
on an index can't work. I guess there exists an optimization opportunity to
just return an emotional set, though it seems easier to not specify an
impossible condition.

As to why it does a table scan, the primary key isn't a separate index. The
rowid primary key is part of the table itself.

On Jan 7, 2018 11:22 AM, "Luuk"  wrote:

> On 07-01-18 19:09, x wrote:
> >> Because reading the whole record (all 3 fields) is more expensive than
> >> just reading the index which has all the info you need to give a correct
> >> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
> > Yes, but the covering index has 2 fields (X & ID). The pk has only 1
> field (ID) so why not use that, particularly in the case where ‘ORDER BY
> ID’ was included in the query?
> >
> >
> sorry, i do not know
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[Scott Robison]

On Tue, Jan 2, 2018 at 5:46 PM, petern  wrote:
> Hi Scott.
>
>>Are there other aggregate functions that take multiple arguments?
>
> Absolutely.  I've got a few in my code which deserialize table rows into
> runtime objects.  Fortunately, the DISTINCT filter makes no sense in that
> use case, so I didn't bump into this issue myself.

Thanks for the info. In doing some quick searches, I found multiple
descriptions of "generic SQL" aggregates that gave a syntax of
"aggregate([DISTINCT|ALL] expression)", which led me to assume that
maybe the standard only allows that syntax with a single expression,
not an expression list. I say maybe because the examples I found were
clearly not the standard.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[Scott Robison]

On Tue, Jan 2, 2018 at 4:15 PM, petern  wrote:
> Hi Tony.  Good. Yes, simpler test case is always better when posting
> possible bugs.
>
> Unfortunately, as Cezary points out, this error is by design (from
> select.c):
>
>if( pFunc->iDistinct>=0 ){
>   Expr *pE = pFunc->pExpr;
>   assert( !ExprHasProperty(pE, EP_xIsSelect) );
>   if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
> sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly one "
>"argument");
> pFunc->iDistinct = -1;
>   }else{
>
> It would be interesting to understand the harm avoided by disallowing
> DISTINCT scope of all the aggregate parameters.   Probably slower, but what
> else?  Usually, there is a comment in the source but not for this one.

I'm not the guy who wrote it or decided how it should work, but it
seems to me that "group_concat(distinct x,y)" would not work
intuitively if y is a variable, though (somewhat surprisingly to me)
it works:

sqlite> create table t(x,y);
sqlite> insert into t values (1,','),(1,'+'),(2,'.'),(2,'-');
sqlite> select group_concat(x,y) from t;
1+1.2-2
sqlite> select group_concat(distinct x,y) from t;
Error: DISTINCT aggregates must have exactly one argument
sqlite> select group_concat(x,y) from (select distinct x, y from t);
1+1.2-2

Are there other aggregate functions that take multiple arguments? I
can't find any examples online of aggregates that take more than one,
which seems like the normal way something like this would be done.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[Scott Robison]

On Tue, Jan 2, 2018 at 1:36 PM, Tony Papadimitriou  wrote:
> create table t(s);
> insert into t values ('A'),('A'),('B');
>
> select group_concat(s,', ') from t group by null;   -- OK
> select group_concat(distinct s) from t group by null;   -- OK
> select group_concat(distinct s,', ') from t group by null;  -- ERROR
>
> -- The moment the optional delimiter is given along with DISTINCT you get 
> this error:
> -- Error: near line 6: DISTINCT aggregates must have exactly one argument

A limitation of the SQL syntax. I suspect this might work for your
example use case: select group_concat(s, ',') from (select distinct s
as s from t) group by null;

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

Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Scott Robison]

On Dec 21, 2017 10:50 AM, "Simon Slavin"  wrote:



On 21 Dec 2017, at 3:46pm, David Raymond  wrote:

> The only potential problem with "insert or ignore into" is that it will
ignore any constraint violation for that record insert

Wait.  What ?

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite>

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you
discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts
would fail, but they would do so silently, without triggering an error
result.


Insert 15 succeeded, 5 failed with error, 6 failed without error. Seems
exactly as advertised. What am I missing?

Note that or ignore is statement specific, not transaction specific, if I
understand correctly.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] StepSqlite: SuperPowers for SQLite and BerkeleyDB

[Scott Robison]

They'll be able to renew the certificate after some payments are made after
the free 6 month trial had lapsed. :)

On Dec 5, 2017 5:15 PM, "Keith Medcalf"  wrote:

>
> Uses an expired SSL certificate ...
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of sub sk79
> >Sent: Tuesday, 5 December, 2017 14:48
> >To: SQLite mailing list
> >Subject: [sqlite] StepSqlite: SuperPowers for SQLite and BerkeleyDB
> >
> >Hi All:
> >
> >The next version of StepSqlite - enhanced-PL/SQL compiler for SQLite
> >and Oracle-TM
> >BerkeleyDB
> >(https://www.metatranz.com/stepsqlite)
> >is due soon and is packed with Super Powers which will blow your
> >mind!
> >
> >For a start, how about powerful Analytic Window Functions,
> >Collections,
> >Bulk-Ops?
> >Now, if you think that is huge, wait, there is a lot more!!!
> >Look for a detailed announcement in first week of New Year 2018.
> >
> >Currently aiming for a January end beta-release after the ongoing
> >rigorous
> >testing and bug-fix cycle.
> >
> >Meanwhile you can sign-up for beta. Everyone signing up for beta -
> >till Jan
> >31, 2018, gets a professional version free for 6 months (July 31,
> >2018).
> >Those already on the list need not re-register - you are already
> >included! There
> >will always be a free developer version (size-limited ).
> >
> >Be the first to experience the power - get on the waiting list:
> >http://www.metatranz.com/stepsqlite/ShowSignUp?guestlogin=ON
> >
> >Happy New Year!!
> >
> >-SKashikar
> >(Founder)
> >__
> >**StepSqlite** enhanced-PL/SQL on Sqlite
> >and BerkeleyDB: *Be Done at the Speed of Lite!*
> >
> >Twitter: https://twitter.com/metatranz
> >Facebook: https://www.facebook.com/Metatranz/
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite and docker performance question

[Scott Robison]

Perhaps the file sync performed by SQLite is more expensive in the docker
environment than in the host. That would make sense to me.

On Nov 30, 2017 7:07 AM, "Sebastien HEITZMANN" <2...@2le.net> wrote:

> In my last mail i have multiple table creation and index. It seam that the
> overtime is for all the create statement.
>
> It really looks like a disk pb. But can't identify so much difference in my
> direct disk access ( with DD )
>
> I will try it on an other host.
>
> 2017-11-30 14:59 GMT+01:00 Simon Slavin :
>
> > OP wrote:
> >
> > > CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
> > > virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
> >
> > Is this the first content of a new file ?  If so, SQLite has to create
> the
> > file and write some structure information as well as writing the table.
> I
> > suspect that the time taken for the overhead is far more than the time
> > taken for the CREATE command.
> >
> > Could you try changing f.sql to create ten tables ?  For example create
> > the table "f1 as above then create tables "f2" to "f1" with the same
> > columns ?  It would be interesting to see what this does to both timings.
> >
> > Simon.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> *Sébastien HEITZMANN*
> Gérant & Directeur technique
> +33 (0)3 89 333 889
> Plus d'info sur : www.2le.net
>
>
>  lentreprise/194148499368?ref=ts>
>   
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please remove multiple warnings from compiler about optimisation, variable conversion, signed overflow and many more potential errors.

[Scott Robison]

On Fri, Sep 29, 2017 at 1:20 PM, Bob Friesenhahn
<bfrie...@simple.dallas.tx.us> wrote:
> On Fri, 29 Sep 2017, Scott Robison wrote:
>>
>>
>> The problem is that there is no one best practice for resolving all
>> such warnings in a way that makes all compilers happy. It is possible
>> to fix all the warnings for one platform, then move on to the next
>> platform and fix all its warnings, and return to the original platform
>> and discover that new warnings have been introduced.
>
>
> My own experience has been that it is possible to write valid C code which
> does not produce warnings at high warning levels on just about any standard
> C compiler.  It is not necessarily a case of "whack a mole". The most
> annoying exception is the Microsoft Visual C Compiler, which produces
> deprecation warnings for standard functions.
>
> One does need to be very careful when fixing compiler warnings so as to not
> introduce new bugs.  The most dangerous warnings to work on are those
> involving signed vs unsigned types.

Except for the fact that the OP called for maximum pedantic warnings.
In that case, you can't reliably fix all the warnings, because
different compilers have different ideas of what maximum means.

In this very thread there is a warning from GCC about

#if SQLITE_4_BYTE_ALIGNED_MALLOC

not being defined. But the standard requires that undefined symbols
being replaced with 0 during preprocessing. How is that warning
useful? It is by definition standard compliant and well defined.

The problem is not just with MSVC.

It is not that warning free code is impossible to create, it just
depends on the details, which sometimes make it very difficult.

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

Re: [sqlite] Please remove multiple warnings from compiler about optimisation, variable conversion, signed overflow and many more potential errors.

[Scott Robison]

On Fri, Sep 29, 2017 at 11:14 AM, Denis V. Razumovsky  wrote:
> I would like to draw attention to the document: "The Power of 10: Rules
> for Developing Safety-Critical Code" from  NASA/JPL Laboratory.
> https://en.wikipedia.org/wiki/The_Power_of_10:_Rules_for_Developing_Safety-Critical_Code

The problem is that there is no one best practice for resolving all
such warnings in a way that makes all compilers happy. It is possible
to fix all the warnings for one platform, then move on to the next
platform and fix all its warnings, and return to the original platform
and discover that new warnings have been introduced.

Additionally, SQLite has never been responsible for the destruction of
a space craft, so maybe SQLite has something right that NASA has
wrong. :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Scott Robison]

There are physical errors and there are logical errors. If an error is
generated from write, it's not unreasonable to classify it as an
"output error". From read as an "input error".

There is a lot of sqlite source code that already exists and has been
written to work with the current interface. That's probably one of the
reasons why extended errors were created, to provide finer
granularity. Regardless of whether it is ideal or not, changing sqlite
in a way that would break existing code is unlikely to happen.

Ultimately it doesn't matter when error codes were added to a given
operating system or which predates what. A decision was made in the
past. The options are to live with decisions that were made in the
past (one I've seen espoused multiple times in this mailing list),
come up with an approach that allows old code to work but exposes new
information (probably the genesis of extended error codes), or break
older code (which I've not seen done deliberately).

I'm not trying to tell you that your point is invalid. It makes sense
in many ways. Short of a time machine I doubt anything will change
(though those decisions are above my pay grade).

That being said, I don't know any non-technical users who are going to
panic that IOERR means their hard drive is dying specifically because
of that text being displayed. Panic perhaps, but not that a hard drive
is about to die. Most people I know don't have that level of
understanding to correlate IO / ERR / hard drive failure rates. They
just think the stupid program is broken and not letting them get their
work done. As for the experienced technical people I know (or at least
me), their first thought would be to investigate the problem, not to
assume their hard drive is failing.


On Tue, Sep 26, 2017 at 2:17 PM, Guy Harris <g...@alum.mit.edu> wrote:
> On Sep 26, 2017, at 1:05 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
>> On 26 Sep 2017, at 8:47pm, Guy Harris <g...@alum.mit.edu> wrote:
>>
>>> On Sep 26, 2017, at 8:22 AM, Jens Alfke <j...@mooseyard.com> wrote:
>>>
>>>> The basic error code is SQLITE_IOERR, which just means "Some kind of disk 
>>>> I/O error occurred” according to the comment. Which is true in this case; 
>>>> an I/O operation returned an error.
>>>
>>> But the *disk* didn't - the *operating system* did, so if SQLITE_IOERR 
>>> really means "Some kind of disk I/O error occurred", it's *not* the right 
>>> error to return for a *permission* error.
>>
>> Those error codes were devised in a day when OS error codes were more simple.
>
> EDQUOT was introduced in 1982, with 4.2BSD; when was SQLITE_IOERR devised?
>
>> Also please note that those error codes are addressed to programmers.  Your 
>> users should never see the text explanation of the number.  Because your 
>> users wouldn’t know what to do about them.
>
> A user wouldn't know what to do with "you've exceeded your stored data 
> quota"?  If so, your site has failed to explain to the users that they've 
> been given a quota, limiting the amount of space on the server that they can 
> use, and that if they exceed their quota, they either need to delete stuff 
> they no longer need, move stuff they might *someday* need but don't need 
> *now* to some archival medium, or ask their system administrator to increase 
> their quota?
>
>> At most the user can be shown the number returned to they can quote it in a 
>> support call.
>
> The *number* might annoy the support staff; right off the top of your head, 
> what's the error number for "file system quota exceeded" or "I/O error"?  (No 
> cheating by looking it up in a man page or include file!)
>
> And, yes, there needs to be *some* way to get the underlying problem reported 
> to somebody in a position to do something about it - where "the underlying 
> problem" includes "what did the OS say?" as much as it includes "what SQLite 
> operation got the error?".
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Support for named foreign keys?

[Scott Robison]

Ah, I misread. Sorry for the noise.

On Sep 24, 2017 4:59 PM, "Keith Medcalf" <kmedc...@dessus.com> wrote:

>
> I don't think so.  The question is about interpreting the schema, not
> about executing anything at all.
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Scott Robison
> >Sent: Sunday, 24 September, 2017 16:47
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Support for named foreign keys?
> >
> >I think he's asking for FK constraint names to be reported in
> >conflict
> >messages which has been requested in the past, but not included up
> >until
> >now because of the approach taken.
> >
> >On Sep 24, 2017 4:16 PM, "Keith Medcalf" <kmedc...@dessus.com> wrote:
> >
> >>
> >> Why do you think this?
> >>
> >> The syntax diagrams are quite clear that FOREIGN KEY constraints
> >can be
> >> named.
> >>
> >> sqlite> create table parent
> >>...> (id integer primary key);
> >> sqlite> create table child
> >>...> (fk integer null,
> >>...> constraint dippydoo foreign key (fk) references
> >parent(id));
> >> sqlite>
> >>
> >>
> >> ---
> >> The fact that there's a Highway to Hell but only a Stairway to
> >Heaven says
> >> a lot about anticipated traffic volume.
> >>
> >>
> >> >-Original Message-
> >> >From: sqlite-users [mailto:sqlite-users-
> >> >boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
> >> >Sent: Sunday, 24 September, 2017 12:06
> >> >To: SQlite User
> >> >Subject: [sqlite] Support for named foreign keys?
> >> >
> >> >Hi,
> >> >
> >> >when will SQLite support the named foreign keys?
> >> >
> >> >I am using SchemaCrawler to visualize my SQLite databases and
> >because
> >> >SQLite does not support it yet, foreign keys get automatically
> >> >generated foreign key names in the diagram.
> >> >
> >> >So it would be nice if SQLite would support it one day. Is'n it?
> >> >
> >> >--
> >> >Best, Pali
> >> >___
> >> >sqlite-users mailing list
> >> >sqlite-users@mailinglists.sqlite.org
> >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >>
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support for named foreign keys?

[Scott Robison]

I think he's asking for FK constraint names to be reported in conflict
messages which has been requested in the past, but not included up until
now because of the approach taken.

On Sep 24, 2017 4:16 PM, "Keith Medcalf"  wrote:

>
> Why do you think this?
>
> The syntax diagrams are quite clear that FOREIGN KEY constraints can be
> named.
>
> sqlite> create table parent
>...> (id integer primary key);
> sqlite> create table child
>...> (fk integer null,
>...> constraint dippydoo foreign key (fk) references parent(id));
> sqlite>
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
> >Sent: Sunday, 24 September, 2017 12:06
> >To: SQlite User
> >Subject: [sqlite] Support for named foreign keys?
> >
> >Hi,
> >
> >when will SQLite support the named foreign keys?
> >
> >I am using SchemaCrawler to visualize my SQLite databases and because
> >SQLite does not support it yet, foreign keys get automatically
> >generated foreign key names in the diagram.
> >
> >So it would be nice if SQLite would support it one day. Is'n it?
> >
> >--
> >Best, Pali
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What's the level of B+-Tree ?

[Scott Robison]

My understanding is that SQLite doesn't use the traditional definition of
b-tree because it doesn't use fixed size records/keys. It will cram as few
or as many as possible.

I'm not in a position to confirm that, but it was something I read a few
years ago I think.

On Aug 11, 2017 9:16 AM, "james ni"  wrote:

> Yes, yes, that's what I'm seeking
>
> 
> From: sqlite-users  on
> behalf of R Smith 
> Sent: Friday, August 11, 2017 18:25
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] What's the level of B+-Tree ?
>
>
> On 2017/08/11 11:08 AM, Clemens Ladisch wrote:
> > james ni wrote:
> >> As in the example that I provided, there are 4 cells in a single btree
> >> page. So there must be some mechanism to determine hoe many keys that
> >> one cell can own.
> > I want to know exactly the very value and just how to change the value
> > to a larger one, for example, 256, 512, or even larger.
> > Keys (and values) can have arbitrary size, so to change how many can fit
> > into a page, make them smaller.  :)
>
> I think perhaps there is a communication failure here. I think the OP is
> looking for a way to change the target key count or maximum allowed keys
> or whatever other value controls how wide a B-Tree page/leaf becomes
> before SQLite decides to de-congest it into two/more new apartments
> (whatever they may be). I /think/ that's what is being asked.
>
> If that is the question - I'm not an expert on it, but I don't think it
> works like that. I think DB Page-sizes dictate those decisions, but I
> might well be wrong.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> sqlite-users Info Page listinfo/sqlite-users>
> mailinglists.sqlite.org
> To see the collection of prior postings to the list, visit the
> sqlite-users Archives. (The current archive is only available to the list
> ...
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

[Scott Robison]

On Fri, Jul 14, 2017 at 11:55 AM, Howard Kapustein
<howard.kapust...@microsoft.com> wrote:
> sqlite> create table blah(id integer primary key not null, x text, y integer 
> not null);
> sqlite> create index blahindex on blah (y);
> sqlite> explain query plan select * from blah where y & ? != 0;
> 0|0|0|SCAN TABLE blah
>
> But other numeric expressions do e.g.
>
> sqlite> explain query plan select * from blah where y = ?;
> 0|0|0|SEARCH TABLE blah USING INDEX blahindex (y=?)
> sqlite> explain query plan select * from blah where y > ?;
> 0|0|0|SEARCH TABLE blah USING INDEX blahindex (y>?)
>
> Is this a known issue?

The indexed value of y in blah is a sorted list of all values from
lowest to highest. It is good for asking questions like "is there a
row where y = something" or "y relates to something". The bitwise and
operator is asking the question "where at least one bit from a set is
not zero".

Let's say you have 100 rows with different values of y. If you ask
for rows "where y & 2 != 0", you should get all the even numbers. The
only way to get that information from an index that includes all
values of y (even and odd) is to scan.

No issue here.

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

Re: [sqlite] VALUES clause quirk or bug?

[Scott Robison]

On Sat, Jul 8, 2017 at 8:50 PM, petern <peter.nichvolo...@gmail.com> wrote:
> The bug here is how the VALUES logic can't have it both ways.  If double
> quotes are invalid for column value literals they should be rejected or at
> least ignored.  They should not suddenly be injected into the column
> name(s) observed by the outer scope of the VALUES clause.   That is of
> course unless there is a software easter egg here that, depending on as yet
> undisclosed well crafted literal values, allows the column names to be
> elegantly specified in addition to the row values.
>
> Also sent through sqlite3_exec():
>
> SELECT * FROM (VALUES ("1",2),(3,4));
> 1,
> 1,2
> 3,4

The ultimate problem here is that long ago a decision was made to be
liberal with interpretation of SQL text so that people had a better
chance of seeing what they expected. Single quoted text is a string,
double quoted text is an identifier. If a string is given where an
identifier is expected, it'll be treated as an identifier in an
attempt to be helpful. If an identifier is given where a string is
expected, and no such identifier exists, it'll be interpreted as a
string instead.

I've read commentary from DRH suggesting that if he had it to do over
again, some of these "helpful" things might not have been done.
Unfortunately, backward compatibility demands that this not be changed
at this point in time.

Further (to me anyway), the keyword "VALUES" seems to explicitly
exclude the option of naming columns. "VALUES" it says, and that's all
you can get from it. I can certainly see the utility of inline
anonymous tables with named columns, but that particular syntax goes
out of its way to omit the potential of naming the columns.

So, should SQLite be pickier in the syntax it supports? Probably. Can
it be changed retroactively and break a bunch of existing code?
Probably not (though it's not my position to say one way or the
other). Are there other syntactic constructs that give you the ability
to have named columns in an otherwise anonymous query? Yes.

Good luck!

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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Scott Robison]

On Jun 28, 2017 6:51 AM, "Simon Slavin"  wrote:



On 28 Jun 2017, at 9:45am, Clemens Ladisch  wrote:

> An explicit NULL works only for the autoincrement column, but not for
default values.

Really ?  In that case I withdraw my previous answer.  I thought that NULLs
were converted to the default value for a column (which is usually NULL but
can be overridden with a DEFAULT clause).  Thanks for the correction.


Depending on needs, one can make a column not null with on conflict
replace, at which point trying to insert or update with null will use the
default, but that is only available if your column can never be null.


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

Re: [sqlite] UTF8-BOM not disregarded in CSV import

[Scott Robison]

On Tue, Jun 27, 2017 at 4:18 AM, Richard Hipp <d...@sqlite.org> wrote:
> The CSV import feature of the SQLite command-line shell expects to
> find UTF-8.  It does not understand other encodings, and I have no
> plans to add converters for alternative encodings any time soon.
>
> The latest version of trunk skips over a UTF-8 BOM at the beginning of
> the input file.

A little late, but it occurred to me how to make this "work" with
older versions of sqlite3 that support readfile / writefile. Say I
have a UTF8 BOM encoded file. I can trim it from SQLite then import
the trimmed version:

sqlite> select writefile('temp.csv', substr(readfile('utf8.csv'), 4));

sqlite> .import temp.csv temp
sqlite> .import utf8.csv utf8
sqlite> .schema
CREATE TABLE temp(
  "a" TEXT,
  "b" TEXT,
  "c" TEXT,
  "d" TEXT
);
CREATE TABLE utf8(
  "?a" TEXT,
  "b" TEXT,
  "c" TEXT,
  "d" TEXT
);

Alternatively, without readfile / writefile support:

sqlite> pragma writable_schema = 1;
sqlite> update sqlite_master set sql = replace(sql, char(0xFEFF), '')
where name = 'utf8';
sqlite> pragma writable_schema = 0;
sqlite> vacuum;
sqlite> .schema
CREATE TABLE temp(
  "a" TEXT,
  "b" TEXT,
  "c" TEXT,
  "d" TEXT
);
CREATE TABLE utf8(
  "a" TEXT,
  "b" TEXT,
  "c" TEXT,
  "d" TEXT
);

Still, not nearly as friendly as sqlite shell doing it for you.

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

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

[Scott Robison]

On Jun 27, 2017 12:13 AM, "Rowan Worth"  wrote:

I'm sure I've simplified things with this description - have I missed
something crucial? Is the BOM argument about future proofing? Are we
worried about EBCDIC? Is my perspective too anglo-centric?


The original issue was two of the largest companies in the world output the
Byte Encoding Mark(TM)(Patent Pending) (or BOM) at the beginning of UTF-8
encoded text streams, and it would be friendly for the SQLite3 shell to
skip it or use it for encoding identification in at least some cases.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8-BOM not disregarded in CSV import

[Scott Robison]

On Jun 26, 2017 9:02 AM, "Simon Slavin"  wrote:

There is no convention for "This software understands both UTF-16BE and
UTF-16LE but nothing else.".  If it handles any BOMs, it should handle all
five.  However, it can handle them by identifying, for example, UTF-32BE
and returning an error indicating that it can’t handle any encodings which
aren’t 16 bit.

Try to be consistent across all fields in your protocol/API.

References:




+1

FAQ quote:

Q: When a BOM is used, is it only in 16-bit Unicode text?

A: No, a BOM can be used as a signature no matter how the Unicode text is
transformed: UTF-16, UTF-8, or UTF-32.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8-BOM not disregarded in CSV import

[Scott Robison]

On Jun 26, 2017 4:05 AM, "Rowan Worth" <row...@dug.com> wrote:

On 26 June 2017 at 16:55, Scott Robison <sc...@casaderobison.com> wrote:

> Byte Order Mark isn't perfectly descriptive when used with UTF-8. Neither
> is dialing a cell phone. Language evolves.
>

It's not descriptive in the slightest because UTF-8's byte order is
*specified by the encoding*.


I fear you may not have read my entire email or at least have missed my
point.

 I'm not advocating one way or
> another, but if a system strips U+FEFF from a text stream after using it
to
> determine the encoding, surely it is reasonable to expect that for all
> supported encodings.
>

?? Are you going to strip 0xFE 0xFF from the front of my iso8859-1 encoded
stream and drop my beautiful smiley? þÿ
Different encodings demand different treatment. BOM is an artifact of
16/32-bit unicode encodings and can kindly keep its nose out of [the
relatively elegant] UTF-8.


One, I'm not going to do anything. Two, clearly I'm taking about the three
byte UTF-8 sequence that decodes to U+FEFF. Three, you are correct about
different encodings. I was trying to move the discussion past the idea of
byte order when what we're really talking about is encoding detection.
ZWNBSP was used for encoding detection because it had a convenient property
that allowed differentiation between multiple encodings and could be safely
ignored. The fact that the Unicode folks renamed it BOM instead of TEI or
BEM or whatever doesn't mean it can't be used with other unicode
transformations. It is neither required, recommended, nor forbidden with
UTF-8, it's up to systems exchanging data to decide how to deal with it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8-BOM not disregarded in CSV import

[Scott Robison]

On Jun 25, 2017 1:16 PM, "Cezary H. Noweta"  wrote:


Certainly, there are no objections to extend an import's functionality
in such a way that it ignores the initial 0xFEFF. However, an import
should allow ZWNBSP as the first character, in its basic form, to be
conforming to the standard.


If we're going to conform to the standard, U+FEFF has been deprecated as
ZWNBSP since Unicode 3.2 in 2002. U+2060 is the Word Joiner now. U+FEFF is
now "reserved" for differentiation of encodings at the beginning of a
stream of text. It may not be required or recommended, but it's not
forbidden either.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8-BOM not disregarded in CSV import

[Scott Robison]

On Jun 26, 2017 1:47 AM, "Rowan Worth"  wrote:

On 26 June 2017 at 15:09, Eric Grange  wrote:

> Alas, there is no end in sight to the pain for the Unicode decision to not
> make the BOM compulsory for UTF-8.
>

UTF-8 is byte oriented. The very concept of byte order is nonsense in this
context as there is no multi-byte storage primitives to worry about.

Making it optional or non-necessary basically made every single text file
> ambiguous
>

Easily solved by never including a superflous BOM in UTF-8 text.


Some people talk about dialing a phone or referring to a remote control as
a clicker, even though most of us don't use pulse based dialing or remote
controls that actually click.

The reality is that interchange of text requires some means to communicate
the encoding, in band or out of band. ZWNBSP (now BOM) was selected as a
handy in band way to distinguish LE from BE fixed size multi-byte text. One
could just as easily call that stupid and demand everyone use network byte
order.

Byte Order Mark isn't perfectly descriptive when used with UTF-8. Neither
is dialing a cell phone. Language evolves.

Maybe people would prefer calling it TEI (Text Encoding Identifier). Then
we could get back to discussion of whether or not stripping U+FEFF from the
beginning of text streams is a good idea. I'm not advocating one way or
another, but if a system strips U+FEFF from a text stream after using it to
determine the encoding, surely it is reasonable to expect that for all
supported encodings. If it doesn't do that for one, it shouldn't do it for
any.

Does SQLite3 support UTF-16 CSV files with BOM/TEI? If not, then UTF-8 need
not. If so, perhaps it should.

As for using a signature at the beginning of UTF-8 text, it certainly can
be useful to distinguish Unicode from code pages & other incompatible
encodings.

That being said, it's not difficult to strip TEI from a file before passing
it to SQLite3 (or any other tool for that matter).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[Scott Robison]

On Tue, Jun 20, 2017 at 8:17 AM, Olivier Mascia  wrote:
>> Le 20 juin 2017 à 15:24, R Smith  a écrit :
>>
>> As an aside - I never understood the reasons for that. I get that Windows 
>> has a less "techy" clientèle than Linux for instance, and that the backwards 
>> compatibility is paramount, and that no console command ever need fall 
>> outside the 7-bit ANSI range of characters... but geez, how much effort can 
>> it be to make it Unicode-friendly? It's not like the Windows API lacks any 
>> Unicode functionality - even Notepad can handle it masterfully.
>
> I wouldn't like looking like I'm trolling this subject, but this is only a 
> matter of I/O functions used by programs built to interact with the display 
> and keyboard when run in a console. Windows needs those programs to use 
> ReadConsoleW/WriteConsoleW to do the proper thing.  Those programs using C 
> library to read or output byte streams can't do anything equivalent no matter 
> what 'codepage' is set to be used or to/from what DBCS the program attempts 
> conversion to/from.
>
> I learned this postulat here last year and have had excellent success with 
> console I/O ever since in my programmings.

About a year ago I had to write an emergency fixup tool for my
employer because of a backward breaking change at Microsoft that was
almost certainly due to a breakdown in revision control. The tool
needed to be localized, but it was sufficiently simple that a console
mode executable was sufficient. I had to jump through hoops to make it
work, but (by way of confirmation), the problems were in the CRT, not
the Win32 API. It was able to write and read Unicode ...

> To be complete, regarding proper display of the output, there is a secondary 
> consideration. The fonts available in Windows are far from covering a large 
> subset of the glyphs.  For eastern languages on a western Windows edition, 
> you generally need to change your console settings to make it use another 
> font than the default one, just so that it can draw the needed glyphs.  But 
> the basic thing to do is get the program running in the console (here we are 
> talking shell.c - sqlite3.exe) to output Windows wide-chars using the 
> function WriteConsoleW(). And use ReadConsoleW() to read wide-chars chunks 
> from the console input, before converting internally to UTF-8 or whatever 
> wanted.

... assuming of course that the locale was using a font that supported
the character set for that area. This was true for our purposes by
default, as we weren't expecting English speaking customers to need to
see Asian languages.

> Sqlite3 shell.c when patched that way is as pleasant to use on Windows 
> console as it can be on a modern Linux or macOS.
>
> Input files feeded to sqlite3.exe need to be in UTF-8, as well as output sent 
> by sqlite3.exe will be: that part is perfectly OK today in sqlite3.exe. Only 
> the keyboard reading and console output writing lacks a little.

Agreed.

>> but geez, how much effort can it be to make it Unicode-friendly?
>
> To further comment on a more general plane than the sqlite3.exe, the issue is 
> deeper in Windows than in its console. Once upon a time (!), they made the 
> choice of 16 bits per characters encoding as the *right* way (their right 
> way!) to do Unicode. It took time for this to evolve, recognizing the need 
> for multi-16 bits words encoding (UTF-16), so they could have chosen UTF-8 
> from day one, but that was not what history recorded. Later UTF-8 got *some* 
> support in the OS (through conversion functions). But never UTF-8 was raised 
> to full citizenship.  There is even a CHCP 65001 to set the 'codepage' to 
> UTF-8. It works partly in some circumstances, but is far from being 'right'. 
> No matter what you would do, there is no way for any file I/O primitive of 
> the OS to take an UTF-8 string as a filename. And this extend to the 
> C-library on Windows platform. The only unicode support is to pass a UTF-16 
> filename through functions ending with a W in the name. Those 'ansi' 
> functions, ending with an A in the name, are merely wrappers converting to 
> the wide chars versions.  There have been numerous requests to Microsoft to 
> let people and developers set the ANSI codepage to UTF-8 so that the file I/O 
> functions taking a narrow char filename string can interpret it as UTF-8. 
> Some are still waiting for that day to come, others use the W-side of things, 
> complicating portability of their codebase. :)

Windows NT was released in 1993. It had been in development for years.
It decided Unicode for I18N/L10N/W6R (WhateveR) purposes was better
than a ton of different code pages. At the point Microsoft committed
to Unicode, it was a two byte / sixteen bit encoding. There was no
UTF-8. There was no UTF-16. Other than endian issues, there was
nothing to worry about. Win32 was an "all new" API.

POSIX people didn't want to re-write the entire API to support 16 bit
characters, 

Re: [sqlite] unusual but trivially reproducible bug

[Scott Robison]

Not a bug. Instead of a keyword, you've defined an alias for the table
named "limit1".

On Jun 19, 2017 4:00 AM, "Robert Cousins"  wrote:

> Summary:
>   Leaving out the space after the word 'limit' causes the limit
> clause to be ignored.
> I've reproduced it on version 3.19.2 2017-05-25 16:50:27
> edb4e819b0c058c7d74d27ebd14cc5ceb2bad6a6144a486a970182b7afe3f8b9
> A sample output is below.
> On one hand, this is a failure to catch a syntax error. On the other
> hand, the fact that it isn't caught is perhaps problematic and indicates
> an underlying parser issue.
> Thanx
>
> sqlite> create table foo(id Integer not null primary key, bar int);
> sqlite> insert into foo values (NULL,1),(NULL,2),(NULL,3);
> sqlite> select * from foo limit1;
> id  bar
> --  --
> 1   1
> 2   2
> 3   3
> sqlite> select * from foo limit2;
> id  bar
> --  --
> 1   1
> 2   2
> 3   3
> sqlite> select * from foo limit 1;
> id  bar
> --  --
> 1   1
> sqlite> select * from foo limit 2;
> id  bar
> --  --
> 1   1
> 2   2
> sqlite>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[Scott Robison]

On Jun 12, 2017 8:26 PM, "Keith Medcalf"  wrote:


Additionally, declaring NOT NULL or NULL is ignored.  CHECK constraints are
honoured.  DEFAULT values are ignored.

so CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000) DEFAULT (-1));
&  CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000));
is CREATE TABLE x(id INTEGER PRIMARY KEY CHECK(id>1000));

This is because an "INTEGER PRIMARY KEY" has a computed default that you
cannot override, and the rowid must always be always NOT NULL ...


Thanks for the info. I'm assuming that the order of constraints is
unimportant so check could come before PK.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[Scott Robison]

On Jun 12, 2017 5:43 PM, "Richard Hipp" <d...@sqlite.org> wrote:

On 6/13/17, Scott Robison <sc...@casaderobison.com> wrote:
>
> Is it fair to say that the rowid aliasing behavior does not require
> (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
> that order as the "type") as long as the type is INTEGER and the
> constraint PRIMARY KEY appears somewhere in the column's constraint
> list?

See https://www.sqlite.org/lang_createtable.html#rowid

   CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);

Columns x *not* a ROWID.  Life is complicated.  Sorry for the mess.
But we have to preserve backwards compatibility - even with bugs like
this.


One,  no problem.

Two, is "INTEGER PRIMARY KEY ASC" the same? I will try it tomorrow.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[Scott Robison]

On Mon, Jun 12, 2017 at 4:20 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
>
> On 12 Jun 2017, at 11:01pm, Scott Robison <sc...@casaderobison.com> wrote:
>
>> Is it fair to say that the rowid aliasing behavior does not require
>> (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
>> that order as the "type") as long as the type is INTEGER and the
>> constraint PRIMARY KEY appears somewhere in the column's constraint
>> list?
>
> FAQ #1 is specifically worded …
>
> <https://www.sqlite.org/faq.html#q1>
>
> But you’re being very specific.  I think you’re right but someone who has 
> read the source code might know otherwise.

The question was raised at work so I got curious about it. It makes
sense to me that the rowid aliasing behavior would only be determined
after lexing and parsing the SQL statement, but the documentation
statements of "INTEGER PRIMARY KEY" being necessary led some people
(understandably) down a path thinking you needed those three words in
that order (perhaps).

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

[sqlite] INTEGER PRIMARY KEY

[Scott Robison]

This is as much out of curiosity as anything. I know that to get the
rowid aliasing behavior for a table one must define the column type as
INTEGER and using the constraint PRIMARY KEY. Something like:

CREATE TABLE A(B INTEGER PRIMARY KEY);

In testing this afternoon I was curious if I could give the constraint a name:

CREATE TABLE A(B INTEGER CONSTRAINT B_PK PRIMARY KEY);

I can, and it is still an alias of the rowid. If I change the type to
INT then it is not an alias. This all makes sense.

If I insert NOT NULL between the type and PK constraint, it still is
an alias (as long as the type is INTEGER).

Is it fair to say that the rowid aliasing behavior does not require
(by design) the incantation "INTEGER PRIMARY KEY" (all three words in
that order as the "type") as long as the type is INTEGER and the
constraint PRIMARY KEY appears somewhere in the column's constraint
list?
-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

[Scott Robison]

On May 9, 2017 9:07 PM, "jose isaias cabrera" <jic...@barrioinvi.net> wrote:


Scott Robison wrote...

On Mon, May 8, 2017 at 11:40 AM, Paul van Helden <p...@planetgis.co.za>
wrote:

> Hi,
>>
>> I use a lot of indexes on fields that typically contain lots of NULLs, so
>> the WHERE NOT NULL partial indexing seems very useful.
>>
>> However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a
>> normal index, SQLite won't use the index to find Max(IndexedField) if it
>> is
>> a partial index.
>>
>> Is this an optimization opportunity? I understand that other kinds of
>> partial indexes might exclude possible Min or Max values, but a NOT NULL
>> index would be fine for mins, maxes and most other things?
>>
>
This may be an optimization opportunity, but you can easily force the
> use of that index by stating WHERE NOT NULL in the select query
> itself. For example:
>
> CREATE TABLE a(b);
> CREATE INDEX ab on a(b) where b is not null;
>
> sqlite> explain query plan select max(b) from a;
> 0|0|0|SEARCH TABLE a
> sqlite> explain query plan select max(b) from a where b is not null;
> 0|0|0|SEARCH TABLE a USING COVERING INDEX ab
>

So, a newbie question here...  What happens if you have,

INDEX ab on a(b);

and no "where b is not null".  Doesn't the INDEX takes care of all those
nulls and the same outcome would result?

thanks.


Yes, it might just need to walk through a larger data set. If nulls are
rare, there probably wouldn't be much of a difference (not in front of a
computer to check). If the column was mostly nulls, and a lot of rows, it
might be a lot faster.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

[Scott Robison]

On Mon, May 8, 2017 at 11:40 AM, Paul van Helden <p...@planetgis.co.za> wrote:
> Hi,
>
> I use a lot of indexes on fields that typically contain lots of NULLs, so
> the WHERE NOT NULL partial indexing seems very useful.
>
> However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a
> normal index, SQLite won't use the index to find Max(IndexedField) if it is
> a partial index.
>
> Is this an optimization opportunity? I understand that other kinds of
> partial indexes might exclude possible Min or Max values, but a NOT NULL
> index would be fine for mins, maxes and most other things?

This may be an optimization opportunity, but you can easily force the
use of that index by stating WHERE NOT NULL in the select query
itself. For example:

CREATE TABLE a(b);
CREATE INDEX ab on a(b) where b is not null;

sqlite> explain query plan select max(b) from a;
0|0|0|SEARCH TABLE a
sqlite> explain query plan select max(b) from a where b is not null;
0|0|0|SEARCH TABLE a USING COVERING INDEX ab

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



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

Re: [sqlite] .DUMP displays floats differently from SELECT

[Scott Robison]

On May 3, 2017 8:07 AM, "Tony Papadimitriou"  wrote:

While trying to search/replace some text from an SQLite3 dump I noticed
that, unfortunately, .DUMP does not produce the exact same numbers as a
plain SELECT on the same values.

I know all about expected floating point inaccuracies, but I don’t see why
it should matter in this case as we have two different places in the same
app (SQLite3) where the same number is ‘displayed’ using whatever default
format.  IMO, in both places the same number should display exactly the
same (i.e., using the same format), both for consistency and easy
verification between dump and actual database.


The select version is output for human readability. The dump version is
output for precision so that every digit goes back into the recreated
database exactly as it came out.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incompatibility into configure.ac

[Scott Robison]

On Apr 5, 2017 7:28 AM, "Bob Friesenhahn" 
wrote:

On Wed, 5 Apr 2017, Richard Hipp wrote:

>
> The deeper issue is that I do not have access to a machine that lacks
> bash on which to test the modifications
>

Specify the shell that configure will use like

  CONFIG_SHELL=/bin/dash ./configure ...


Again, I'm not a posix guru, but according to
http://stackoverflow.com/questions/5725296/difference-between-sh-and-bash
...

bash supports a --posix switch, which makes it more POSIX-compliant. It
also tries to mimic POSIX if invoked as sh.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incompatibility into configure.ac

[Scott Robison]

On Tue, Apr 4, 2017 at 9:52 PM, Jens Alfke <j...@mooseyard.com> wrote:
>
>> On Apr 4, 2017, at 8:33 PM, Pavel Volkov <pavelivol...@gmail.com> wrote:
>>
>> bash it's Bourne again shell. Not Bourne shell. Bash is more then POSIX
>> shell.
>
> Yes, that’s what they said. The Bourne shell is ‘sh’.
>
>> And you forgot the FreeBSD in your listing. As example. It does not use bash 
>> at all.
>
> It doesn’t come with bash installed (presumably as an aftereffect of the 
> ancient religious war between BSD and System V; tcsh was a BSD invention.) 
> But so what? You can easily install it through a package manager. If that 
> means SQLite has a dependency on it, that’s nothing awful; most software has 
> dependencies on other software, and package managers track those dependencies 
> very well.
>
> The issue here seems to be that some scripts in the SQLite source 
> distribution are _implicitly_ assuming that the default shell is bash, or 
> else that ‘sh’ is an alias of bash. The best fix, IMHO, would be to make 
> those scripts explicitly invoke bash, using a shebang or whatever.

I'm not as conversant with posix systems, but there isn't a standard
location for bash on systems where it is not the default shell. Does
sh not provide the necessary functionality with some other syntax?

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



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

Re: [sqlite] core using sqlite 3.17.0 on solaris

[Scott Robison]

On Thu, Mar 30, 2017 at 11:22 AM, Richard Hipp <d...@sqlite.org> wrote:
> On 3/30/17, Scott Robison <sc...@casaderobison.com> wrote:
>>>
>> Also, isn't the new code potentially allocating a smaller buffer in
>> zSpace? If sizeof(yyParser) is 15, the removed line would allocate a
>> 15 element array of unsigned char objects for a total of 15 bytes. The
>> added line would allocate a 15/8 = 1 element array of sqlite3_uint64
>> objects for a total of 8 bytes.
>>
>
> The code that I actually checked in fixed that.
> https://www.sqlite.org/src/artifact/d62a8f87?ln=486
>
> But that is an academic question now that the problem is fixed
> properly, per Clemens' suggestion.
> https://www.sqlite.org/src/artifact/de2ec4fe?ln=485

Right. I posted 60 seconds or so before your email hit my inbox. :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] core using sqlite 3.17.0 on solaris

[Scott Robison]

On Thu, Mar 30, 2017 at 10:44 AM, Clemens Ladisch <clem...@ladisch.de> wrote:
> Richard Hipp wrote:
>>  #ifdef sqlite3Parser_ENGINEALWAYSONSTACK
>> -  unsigned char zSpace[sizeof(yyParser)];  /* Space for parser engine 
>> object */
>> +  /* Space to hold the Lemon-generated Parser object */
>> +  sqlite3_uint64 zSpace[sizeof(yyParser)/sizeof(sqlite_uint64)];
>>  #endif
>
> The yyParser type is known at this place (otherwise, sizeof() would not
> work).  So why isn't a variable of this type defined directly?

Also, isn't the new code potentially allocating a smaller buffer in
zSpace? If sizeof(yyParser) is 15, the removed line would allocate a
15 element array of unsigned char objects for a total of 15 bytes. The
added line would allocate a 15/8 = 1 element array of sqlite3_uint64
objects for a total of 8 bytes.

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

Re: [sqlite] Possibly pointless assert

[Scott Robison]

On Thu, Mar 23, 2017 at 11:05 AM, Dan Kennedy <danielk1...@gmail.com> wrote:
> On 03/23/2017 11:46 PM, Scott Robison wrote:
>>
>> Note: I'm on Windows 10 and reproduced this with the amalgamation
>> downloaded today from
>> http://sqlite.com/2017/sqlite-amalgamation-317.zip
>>
>> Step 1: Using sqlite3 shell, created a database test.db with the
>> following schema:
>>
>>  CREATE TABLE a(b text collate binary, c text collate nocase);
>>  CREATE INDEX ab on a(b);
>>  CREATE INDEX ac on a(c);
>
>
> Thanks for reporting this. Removed the assert() here:
>
>   http://www.sqlite.org/src/info/9f2e04d3c3526b5f

Glad to do it. Complete fluke that I came across it. Still, nice to
"contribute" something, even if this trivial. :)

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

Re: [sqlite] Possibly pointless assert

[Scott Robison]

On Thu, Mar 23, 2017 at 10:17 AM, Scott Robison <sc...@casaderobison.com> wrote:
> On Thu, Mar 23, 2017 at 9:21 AM, Dan Kennedy <danielk1...@gmail.com> wrote:
>> How did you trip the assert()? i.e. what is the database schema and query
>> that cause it to fail?
>
> In trying to track down issues recently, a team member defined
> SQLITE_DEBUG. My "fix" was to simply undefine SQLITE_DEBUG, thus
> compiling out the assertions anyway. Since we won't have it in
> production code, I wouldn't call it a bug, just an over enthusiastic
> bit of error prevention.
>
> The query was apparently a vacuum. I'll synthesize a test case and
> submit it later.

Note: I'm on Windows 10 and reproduced this with the amalgamation
downloaded today from
http://sqlite.com/2017/sqlite-amalgamation-317.zip

Step 1: Using sqlite3 shell, created a database test.db with the
following schema:

CREATE TABLE a(b text collate binary, c text collate nocase);
CREATE INDEX ab on a(b);
CREATE INDEX ac on a(c);

Note: I did not insert any data. It is not necessary.

Step 2: Copied the 35 line sample C code from
http://sqlite.com/quickstart.html into sqlite-assert-test.c. My only
change was to change the sqlite3.h include from  to
"sqlite3.h"

Step 3: Extracted the amalgamation files into the directory with
sqlite-assert-test.c.

Step 4: Opened a 64 bit native build command prompt from Visual C++ 2015.

Step 5: Build the test program as follows:

cl /Zi -DSQLITE_DEBUG sqlite-assert-test.c sqlite3.c

Step 6: Ran the resulting executable as:

sqlite-assert-test.exe test.db vacuum

It throws an assertion, presumably trying to vacuum the ab index.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possibly pointless assert

[Scott Robison]

On Thu, Mar 23, 2017 at 9:21 AM, Dan Kennedy <danielk1...@gmail.com> wrote:
> On 03/23/2017 04:45 AM, Scott Robison wrote:
>>
>> Take a look at
>> http://www.sqlite.org/cgi/src/artifact/3ed64afc49c0a222?ln=2214,2233
>> (especially the assert within).
>>
>> I may not be understanding something, but that assert seems pointless
>> to me.
>
>
>
> The assert() says that if the buffer pointed to by Column.zColl contains the
> string "BINARY", then it must point actually point to global buffer
> sqlite3StrBINARY, not to some other buffer that contains the same bytes.
> i.e. the assert() means that the next line could be written as:
>
>   if( sqlite3StrBINARY==zColl ) break;
>
> instead of:
>
>   if( sqlite3_stricmp(sqlite3StrBINARY, zColl) ) break;
>
> I think it's likely an oversight that that line was not rewritten. Or
> perhaps just a choice to take the safer option in case the assert() is not
> actually true. There is another part of the code where that assumption is
> made and the (sqlite3StrBINARY==zColl) comparison is used, although I think
> it's just an optimization - SQLite should not return the wrong answer even
> if the assert() can be false.

Correct. If the line following was just using an equality comparison
of two pointers, the assert would make a bit more sense. Given that
the following comparison uses the "safer" stricmp, the assertion seems
pointless. It winds up asserting something is wrong when in fact the
code works exactly as advertised.

>
> How did you trip the assert()? i.e. what is the database schema and query
> that cause it to fail?

In trying to track down issues recently, a team member defined
SQLITE_DEBUG. My "fix" was to simply undefine SQLITE_DEBUG, thus
compiling out the assertions anyway. Since we won't have it in
production code, I wouldn't call it a bug, just an over enthusiastic
bit of error prevention.

The query was apparently a vacuum. I'll synthesize a test case and
submit it later.

>
> Dan.
>
>
>
>
>
>
>
>> The point of the loop is to check all the columns in an index
>> to see if they are all binary collated. If any column is not binary
>> collated, then exit early, which will skip the following if statement
>> at 2234.
>>
>> It feels to me like that assert was added as a mid-development sanity
>> check when it was being developed against a known database. I had it
>> trip on me today unexpectedly.
>>
>> If I am incorrect and that is a useful assertion, I'd like to
>> understand the reason why. Otherwise, the if statement at 2232 does
>> everything the assert at 2230 does, making the assert fire when the
>> code is working correctly.
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

[sqlite] Possibly pointless assert

[Scott Robison]

Take a look at 
http://www.sqlite.org/cgi/src/artifact/3ed64afc49c0a222?ln=2214,2233
(especially the assert within).

I may not be understanding something, but that assert seems pointless
to me. The point of the loop is to check all the columns in an index
to see if they are all binary collated. If any column is not binary
collated, then exit early, which will skip the following if statement
at 2234.

It feels to me like that assert was added as a mid-development sanity
check when it was being developed against a known database. I had it
trip on me today unexpectedly.

If I am incorrect and that is a useful assertion, I'd like to
understand the reason why. Otherwise, the if statement at 2232 does
everything the assert at 2230 does, making the assert fire when the
code is working correctly.

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

Re: [sqlite] SQLite3.dll for x64

[Scott Robison]

On Mon, Mar 6, 2017 at 3:48 AM, Anick Saha <anick.gem...@gmail.com> wrote:

> Hi,
>
> Please look into this issue:
> http://stackoverflow.com/questions/42623284/sqlite3-dll-for-x64
>
> My guess would be that the download link might be directing to the wrong
> set of files.
>

As you've already discovered (based on the posted answer), you just needed
an extra command line option passed to the lib tool.

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

Re: [sqlite] [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

[Scott Robison]

On Mon, Mar 6, 2017 at 3:39 AM, Yuri <y...@rawbw.com> wrote:

> On 03/06/2017 01:00, Dominique Devienne wrote:
>
>> This is clearly documented inhttps://www.sqlite.org/c3ref/blob_open.html
>> though, so I'm afraid this is "by design". --DD
>>
>
>
> Even though this is documented, parts of this limitation don't appear to
> be reasonable. Updating an integer field in the same row shouldn't affect
> the blob field. Rows can be very large and shouldn't move when individual
> fields are updated.


The potential difficulty here is that integers are not encoded as fixed
sized fields. Depending on the magnitude of the integer that is being
written, it could change sizes from 0 to 9 bytes. Thus the offset of the
blob in the row may change.

Once the fix for the "any table same rowid" problem is available to you,
your best bet is the separate blob table (which I think I read you've
already tried).
-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] thousand separator for printing large numbers

[Scott Robison]

On Feb 11, 2017 7:15 PM, "James K. Lowden"  wrote:

On Fri, 10 Feb 2017 10:46:24 +0100
Dominique Devienne  wrote:

> PS: In this context, I don't want to use a host-program provided UDF.
> This is data meant to be viewed with any SQLite client, so kind of
> "report".

https://github.com/jklowden/sqlrpt

While Clemens was parsimonously adding 14 lines to support a thousands
separator in the SQL interpreter, I was extravagantly adding 225 to
create a new utility.


{snip}

I thought it was DRH that added it? Regardless, what everyone seems to
ignore, is the stated desire for a query that works with any stock
compatible SQLite implementation. So it can be used with the sqlite shell,
or any of the sqlite embedded database managers or library wrappers for a
variety of languages.

Sure, in a perfect world, people would use something like your new utility.
Thank you for it. But given that SQLite already uses certain American-isms
(decimal point for string coerced reals), adding this is not some horrible
affront to software development, any more than a lack of "type safety" is.
It's just a tool that you are free to ignore if it doesn't suit your use
one's use case.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Scott Robison]

On Wed, Feb 1, 2017 at 3:48 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> > On Tue, 31 Jan 2017 15:50:08 -0800 James K. Lowden <
> jklow...@schemamania.org> wrote:
>
> > Last year there was much rejoicing when Microsoft decided to bundle
> > SQLite with Windows.  That leaves me with a new question: if SQLite
> > announced its intention to move to C11 in 2018, would that perhaps
> > influence Microsoft's timeline to update its compiler?
>
> No.  Microsoft products require multiple versions of multiple Microsoft
> and third-party compilers to compile any of their products.  They will
> simply add whatever is needed to their internal compilers suite and use
> that to build the winsqlite.dll for distribution with Windows.  Windows
> does not use ICU and does not support the use of timezones, etc, so there
> is no need for them to update their compilers at all as they will never use
> anything but the most primitive of any feature available.
>
> That said, there is no problem with Visual Studio compiling the ICU module
> as it was -- it works just fine without error.  Just that when set to
> pedantic mode it produces a higher level of messages, whether they be a
> true statement of fact or not.
>
> The only issue I've run into using a Microsoft compiler is that it does
> not handle in-block initialization and declarations -- they all have to be
> at the top of a function before the first "executable" statement.  I
> believe that was a C language restriction back in the early 70's.
>

The declaration of variables have to be at the top of a scope as per ANSI
C. C99 relaxed that.
-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Scott Robison]

On Jan 31, 2017 6:25 PM, "James K. Lowden"  wrote:

On Tue, 31 Jan 2017 15:50:08 -0800
Nathan Bossett  wrote:

> Since this is the sqlite users list and not the dev's list, can I ask
> what your use case is that writing a thin wrapper around SQLITE
> doesn't solve your problem?

I've encountered no problem compiling SQLite header files as C11.

The OP encountered a problem compiling SQLite itself as C89 because
there was a C99 construct his compiler didn't support.  The fix was to
"update" the code to conform to a 17-year old standard.

I wondered aloud what system still exists that supports only C89.  On
review of this thread, there is indeed one such compiler in widespread
use: Microsoft Visual Studio.[1]


An older Visual C++ compiler. Not all versions. Anyone could download a
newer compiler for Windows, even Visual C++. See
http://stackoverflow.com/questions/9610747/which-c99-features-are-available-in-the-ms-visual-studio-compiler
for some commentary on feature support.

Windows doesn't come with a compiler, so telling someone on Windows they
need a newer compiler isn't the end of the world, necessarily. Not all
platforms have as much support as Windows. Not that one needs to support
all those platforms. Say cc65... Probably isn't going to ever work.

Despite the fact that the Windows API is defined in terms of C,


ANSI C. And C++. And C# / CLR. Just not C99.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bitten by lack of isolation between SELECT and UPDATE on the same connection

[Scott Robison]

On Tue, Jan 31, 2017 at 12:15 PM, Jens Alfke  wrote:

>
> > On Jan 31, 2017, at 9:39 AM, James K. Lowden 
> wrote:
> >
> > According the SQL standard, every SQL statement is atomic.  SELECT has
> > no beginning and no end: the results it returns reflect the state of
> > the database as of the moment the statement was executed.  If you fetch
> > the last row six days after the first, it still belongs to the database
> > as it stood when you began.
>
> That is the behavior I was assuming and desiring, but it’s not what
> actually occurs. If there are concurrent mutations in the same connection,
> the rows returned by SELECT do _not_ reflect the prior state of the
> database, but suffer from “undefined” behavior. In other words, there is a
> lack of isolation between the SELECT and the concurrent UPDATEs.
>
> It’s possible I’m misunderstanding your point, though!
>
> My immediate workaround (implemented last night) is to iterate over the
> statement at the moment the query is run, saving all the rows in memory.
> Our enumerator object then just reads and returns successive rows from that
> list.
>
> In the medium term I have ideas for optimizations that can let us avoid
> this memory hit in most circumstances (since most queries are not made at
> the same time as mutations.) For example, I could use the original
> enumerator behavior by default, but when the client requests a mutation I
> first notify all in-progress enumerators [on that connection], which will
> immediately read the rest of their rows into memory.
>

I think you said something earlier about a fear that the record set might
be too big to fit in memory (or wanting to avoid that possibility). You
could select the record set you want to a temp table then select *that*
while running updates on the original tables. Probably something you
already thought of (or maybe I subconsciously read it from someone else
already; sorry if adding noise), but thought I'd toss it out.

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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Scott Robison]

On Tue, Jan 31, 2017 at 9:48 AM, James K. Lowden <jklow...@schemamania.org>
wrote:

> On Mon, 30 Jan 2017 13:32:46 -0700
> Scott Robison <sc...@casaderobison.com> wrote:
>
> > Basing source on "ANSI C" (as much as possible) just gives you the
> > biggest possible distribution / compatibility.
>
> Yes, but it also limits you to C as it stood 20 years ago.  And
> counting.  Is there no point at which a more recent standard should be
> adopted?  Among features of C11 I use:
>
> stdint.h
> stdbool.h
> VLA
> designated initialization
>

{snipped}


>
> Knowing what I do of the philosophy of the SQLite developers -- if we
> can speak of such -- I honestly think they (you) would find C11
> amenable.  It's a better language.  It's every bit as respectful of its
> environment, and is more standardized and more easily conformed to it.
>

I agree with all your points regarding the superiority of C99 to ANSI C.
The only downside to it is the "baked in" compatibility. I'm not aware of
any C99 conforming compiler that does not also claim to support C89, but
not vice versa.

It doesn't really matter much to me, as I'm not personally trying to target
such platforms. When I write my own code, I prefer C++11 or later,
personally. But I'm not trying to provide a robust library that works
practically everywhere, either.

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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Scott Robison]

It's an iBook, so some version of OS X I would guess. From a little
searching online, 10.2 was the last release in 2002, and it seems that it
may have included GCC 3.3. GCC 3.3 did not fully support C99 (as its
release notes indicated "A few more ISO C99 features now work correctly."
Of course, things could have been upgraded in which case this is all moot.

I appreciate the desire to be using as modern of a toolset as possible.
Basing source on "ANSI C" (as much as possible) just gives you the biggest
possible distribution / compatibility. Not that you can just ignore other
issues, of course, but given that a fix for this issue has already been
committed, and allows the code to work with both C89 & C99, the original
report is no longer a consideration.


On Mon, Jan 30, 2017 at 12:08 PM, James K. Lowden <jklow...@schemamania.org>
wrote:
>
> On Sun, 29 Jan 2017 21:40:23 -0500
> Richard Hipp <d...@sqlite.org> wrote:
>
> > On 1/29/17, James K. Lowden <jklow...@schemamania.org> wrote:
> > >
> > > I wonder what pricey embedded environment both supports dlopen(2)
> > > and does not support C99, in this day and age.
> >
> > One of the test platforms for SQLite is an old iBook I bought back in
> > approximately 2002.  Dunno if it support C99 or not, but I suspect
> > not, as there is quite a bit it does not support.
>
> What compiler and OS are you using?  The pcc and gcc compilers both
> support C99 on the PPC architecture.
>
> > This is a important test platform because it uses a PPC CPU, which
> > means it is big-endian and thus serves to verify that SQLite works on
> > both big-endian and little-ending machines and that the database files
> > are freely interchangeable.
>
> I am glad you do this.  I used to do the same for FreeTDS using
> Sparcstation.
>
> If you are interested in upgrading the SQLite core to C99, I'm
> willing to do the legwork and can supply the needed paperwork.  I know
> we can find a compiler for your PPC machine, and I bet if need be we can
> port pcc to whatever you're running.   (ISTM enlisting pcc would add to
> SQLite's portability, btw.)
>
> --jkl
>
>
> _______
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users




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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Scott Robison]

On Jan 29, 2017 5:49 PM, "Simon Slavin" <slav...@bigfraud.org> wrote:


On 30 Jan 2017, at 12:06am, Scott Robison <sc...@casaderobison.com> wrote:

> I'm not sure how big the market is, but there are older computers in use
in
> areas that might be running older OS because anything newer is too
bloated.

The emphasis is shifted to non-modern compilers because a large proportion
of the literally billions of installations of SQLite are on embedded
computers, not the sort of computers people sit at and type on.  SQLite is
inside TVs, TV recorders, WiFi routers, Point Of Sale tills, elevator
controllers, SatNav units, weather monitoring stations and, my recent
favourite discovery, those machines in car parks which take your money and
print your ticket.  Some machines of the above types.  Probably not all of
them.


Understood and agreed. I'm just observing that not everyone is even using a
modern machine as a desktop workstation.


Those machines use small cheap processors, designed a long time ago, which
are addressed with old compilers which support old versions of C.  Ten
years old is not uncommon, especially if the device doesn’t need to support
a graphical user interface.

So a round of bringing SQLite3 up to date to support shiny new versions of
C is likely to introduce problems for many programmers unless you’ve hired
the sort of C lawyer who has all the changes in his or her head.  Better to
make changes just when people point out problems.  SQLite4 can be a new
start, with a stated set of compilers supported.

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

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

[Scott Robison]

I'm not sure how big the market is, but there are older computers in use in
areas that might be running older OS because anything newer is too bloated.

If maintaining ANSI C compatibility truly becomes a burden, sure. If it
isn't then why not?

On Jan 29, 2017 4:36 PM, "James K. Lowden"  wrote:

> On Sat, 28 Jan 2017 11:49:19 +1100
> "dandl"  wrote:
>
> > >>>Sticking with C90 is perfectly rational if you're still running
> > >>>Windows 98 on a Pentium III at 500 Mhz with 256 MB RAM.  Else,
> > >>>really, it's not too soon to adopt a 6-year old standard, C11.
> >
> > There are plenty of older C compilers used in various niche
> > applications: embedded, pricey vendor dev kit not updated, etc.
> > Obviously not many of them using Sqlite, but you never know...
>
> I wonder what pricey embedded environment both supports dlopen(2) and
> does not support C99, in this day and age.
>
> And I'm unsympathetic. SQLite is in the public domain, so that anyone
> can use it however they like.  That doesn't oblige SQLIte to cater to
> the most unpublic, renegade environments.  If some proprietary software
> can't take advantage of something made freely available, I would wish
> only for tougher noogies.
>
> --jkl
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[Scott Robison]

This might be helpful. Maybe not. It's not an answer to the exact question,
but ...

What if you were to set all the IDs to their negative, then update them as
desired?

UPDATE TABLEA SET ID = -ID;
UPDATE TABLEA SET ID = -ID + 1;

Or something like that. It is not as efficient as would be preferred, but
it should avoid the problem. Assumes you aren't using negative primary keys
or foreign keys...

On Jan 23, 2017 8:54 AM, "Clemens Ladisch"  wrote:

Cecil Westerhof wrote:
>> UPDATE desktops
>> SET indexNo = indexNo  + 1
>>
>> But it does not, it gives:
>> Error: UNIQUE constraint failed: desktops.indexNo
>
> ​It is actually quite simple:
> PRAGMA ignore_check_constraints = ON

A UNIQUE constraint is not a CHECK constraint.


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

Re: [sqlite] Changing ID's to UUID

[Scott Robison]

On Tue, Nov 15, 2016 at 1:38 PM, Jens Alfke <j...@mooseyard.com> wrote:

>
> > On Nov 15, 2016, at 11:35 AM, Quan Yong Zhai <q...@msn.com> wrote:
> >
> > Create a custom function MD5 ,
>
> If you’re going to go to this trouble, at least use SHA256!
>
> MD5 is broken. These days no one should be using it for anything, except
> when needed for compatibility with legacy formats/protocols.
>

Completely depends on your needs. If your needs are not cryptographic, then
there is no problem. Just like there is no problem using a pseudo random
number generator for non cryptographic purposes.

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

Re: [sqlite] deprecated windows OS calls

[Scott Robison]

On Fri, Nov 4, 2016 at 7:28 PM, Mark Benningfield <mbenningfie...@cox.net>
wrote:

> Hello all.
>
> I'm building SQLite from the amalgamation:
>
> #define SQLITE_VERSION"3.14.1"
> #define SQLITE_VERSION_NUMBER 3014001
> #define SQLITE_SOURCE_ID  "2016-08-11 18:53:32
> a12d8059770df4bca59e321c266410344242bf7b"
>
> I'm getting the following compiler warnings:
>
> WarningC4996'GetVersionExA': was declared deprecated sqlite3
> WarningC4996'GetVersionExW': was declared deprecated sqlite3
>
> Is this something I should be concerned about with deployment on Windows
> 8/10 boxes?
>

As long as SQLite isn't using APIs introduced with Windows 8 or later, it
shouldn't make a difference. Microsoft might call GetVersionEx "deprecated"
but it's still there and supported. The only catch is that it will
(apparently) forever report it is an older version of the OS than it really
is.

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

Re: [sqlite] A small technical question about SQLite

[Scott Robison]

Or something.

{google}

Nothing obvious from a search. It must be safe!

On Mon, Oct 24, 2016 at 8:49 PM, Jens Alfke <j...@mooseyard.com> wrote:

> I’m guessing it’s probably a phone-sex line. No one would actually expect
> strangers to call them to answer their homework problems.
>
> —Jens
>
> > On Oct 24, 2016, at 7:20 PM, Scott Robison <sc...@casaderobison.com>
> wrote:
> >
> > Don't everyone dial at once!
> >
> > On Mon, Oct 24, 2016 at 2:36 PM, LIAT SEAGAL-DERY <
> liat.seagald...@gmail.com
> >> wrote:
> >
> >> Hi,
> >>
> >> I am a student in SUNYPOLY, working on my homework. I have a small
> >> question.
> >> Can someone please contact me? I prefer a phone call at 585-473-6501
> >>
> >> Thank you,
> >> Liat
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > Scott Robison
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] A small technical question about SQLite

[Scott Robison]

Don't everyone dial at once!

On Mon, Oct 24, 2016 at 2:36 PM, LIAT SEAGAL-DERY <liat.seagald...@gmail.com
> wrote:

> Hi,
>
> I am a student in SUNYPOLY, working on my homework. I have a small
> question.
> Can someone please contact me? I prefer a phone call at 585-473-6501
>
> Thank you,
> Liat
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Problem with rename table

[Scott Robison]

On Sep 10, 2016 2:54 AM, "John McMahon"  wrote:
>
> On 08/09/2016 10:09, Bob McFarlane wrote:
>>
>> Please reply if you sent this. Thanks.
>>
>
> Hmm, looks like a fishing exercise to me. Same message in several threads.
>
> This reply only to mailing list.

It's an anti-spam measure. Most spam will either not get the auto generated
message, or if it is a forgery the victim has a chance to disavow the
content.

I think it is too heavy handed a technique personally, but to each their
own.

I sent the sender an email letting him know that his anti-spam system was
spamming the crap out of the list and he fixed it. Easy peasy.

>
> John
>
>>
>>
>>
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On
>> Behalf Of Richard Hipp
>> Sent: Wednesday, September 7, 2016 7:49 PM
>> To: SQLite mailing list 
>> Subject: Re: [sqlite] Problem with rename table
>>
>> On 9/6/16, Radovan Antloga  wrote:
>>>
>>> Hi Richard !
>>>
>>> I can't find a solution how to fix my database after I have renamed
>>> table DOKUMENTI to DOKUMENTI2.
>>> Table DOKUMENTI had trigger dokumenti_trigger1 and after renaming
>>> table I cant execute any sql. I forgot to drop trigger first. So now I
>>> always get error:
>>> malformed database schema (dokumenti_trigger1) - no such table
>>> main.dokumenti.
>>
>>
>> Rename the table back to its old name?
>>
>> Worst case:  You can drop all the triggers like this:
>>
>>PRAGMA writable_schema=ON;
>>DELETE FROM sqlite_master WHERE type='trigger';
>>
>> Then close and reopen your database, and you have no more triggers.
>> The same will work for views.  But if you try the above with tables or
>> indexes, you'll end up with a database that fails "PRAGMA
integrity_check" -
>> though the corruption can be fixed with a VACUUM.
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> TMGID:S1141121912621011
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
> Regards
>John McMahon
>   li...@jspect.fastmail.fm
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[Scott Robison]

Darn. Oh well.

On Sep 8, 2016 9:34 AM, "Stephen Chrzanowski"  wrote:

> Apparently, no.  Error is "cannot create trigger on system table".
>
> On Thu, Sep 8, 2016 at 11:19 AM, Stephen Chrzanowski 
> wrote:
>
> > Interesting idea.  I'll try that on a scratch DB when I get the
> > chance.  (Spent too much time in this thread, rather than doing work I'm
> > being paid to do. heh)
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[Scott Robison]

On Sep 8, 2016 8:16 AM, "Richard Hipp"  wrote:
>
> On 9/8/16, Stephen Chrzanowski  wrote:
> >
> > However, the rabbit I was hoping to pull out of the hat was that the
change
> > in version numbers be done automatically when I make a change in the 3rd
> > party DB management tool.

I don't know if this would work, but maybe one could add a trigger to
sqlite_master that would automatically update the integer whenever
something happens to literally change the schema. That way third party
tools would keep the number up to date as well as your own custom code
without needing a user defined function.

>
> Perhaps make your application schema-version number a hash of the SQL
> for the schema, like this:
>
>SELECT md5sum(sql) FROM (
>   SELECT sql FROM sqlite_master
>WHERE sql IS NOT NULL
>ORDER BY sql
>);
>
> You will need to add the md5sum() aggregate function yourself.  A
> sample implementation can be found in the SQLite source tree (used for
> testing) here: https://www.sqlite.org/src/artifact/bdae822f2?ln=3894-3932
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Scott Robison]

I saw no page content below "Documents By Category". If I rotated it to
landscape the content was there, but it didn't render.

I'll be able to go more in depth later, not at home with the tablet at the
moment.

On Sep 6, 2016 12:37 PM, "Richard Hipp" <d...@sqlite.org> wrote:

> On 9/6/16, Scott Robison <sc...@casaderobison.com> wrote:
> > Documents by category doesn't like
> > portrait mode on my tablet (at least).
>
> What's it doing wrong?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Scott Robison]

Lamding page looks fine on my Galaxy Note 4 phone and cheap Amazon Fire
tablet, both landscape and portrait. Documents by category doesn't like
portrait mode on my tablet (at least).

On Sep 6, 2016 8:38 AM, "Eric Kestler"  wrote:

> Looks quite good and is very readable on my iPhone 6s and iPad Mini 4,
> both portrait and landscape modes.
>
> ..Eric
>
> __
> Plan A is always more effective when the device you are working on
> understands that Plan B involves either a large hammer or screwdriver
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] client app crashes frequently and points sqlite DLL

[Scott Robison]

In looking at the changelog for 2.7.12, I see multiple SQLite related
Python fixes that addressed coredump bugs.

On Sep 1, 2016 9:22 AM, "Scott Robison" <sc...@casaderobison.com> wrote:

> It appears 2.7.12 is available. Perhaps it is worth trying.
>
> On Sep 1, 2016 9:19 AM, "Frantz FISCHER" <ffisc...@nuxeo.com> wrote:
>
>> Hello,
>>
>>
>> 2.7.10 python own release.
>>
>> Got another crash mentioning the _pylite3.pyd module as you suspected
>> before.
>>
>>
>> Thanks,
>>
>> Frantz.
>>
>>
>> On 01/09/2016 17:00, Scott Robison wrote:
>>
>>> Specifically, what version / distribution of Python 2 is in use? Python's
>>> own release, ActiveState, other?
>>>
>>> On Sep 1, 2016 8:27 AM, "Frantz FISCHER" <ffisc...@nuxeo.com> wrote:
>>>
>>> Hello Richard,
>>>>
>>>>
>>>> Thank you for your answer!
>>>>
>>>> Could you please tell me which details you would require?
>>>>
>>>> I managed to get a windows mini dump, would that be of any help?
>>>>
>>>> This is the default sqlite3.dll bundled with python 2.7. Do you require
>>>> the python module? the dll itself?
>>>>
>>>>
>>>> Regards,
>>>>
>>>> Frantz.
>>>>
>>>>
>>>> On 01/09/2016 14:33, Richard Hipp wrote:
>>>>
>>>> On 9/1/16, Frantz FISCHER<ffisc...@nuxeo.com>  wrote:
>>>>>
>>>>> Using our client Python app for synchronization with a server it
>>>>>> crashes
>>>>>> frequently generating c005 error and also mentioning sqlite3.dll.
>>>>>>
>>>>>> Usually when an application crashes and SQLite is mentioned in the
>>>>> stack trace, that indicates that some other module corrupted the heap
>>>>> and SQLite was the first unlucky module to stumble over the
>>>>> corruption.
>>>>>
>>>>> That said, if you can provide more details, we will look into the
>>>>> matter.
>>>>>
>>>>>
>>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@mailinglists.sqlite.org
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>> --
>>
>> *Frantz Fischer*
>> Premium Support Team
>> +33 (0)1 40 33 79 87
>> +33 (0)1 40 33 71 41 (Fax)
>> www.nuxeo.com - Content Management Platform <https://www.nuxeo.com>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] client app crashes frequently and points sqlite DLL

[Scott Robison]

It appears 2.7.12 is available. Perhaps it is worth trying.

On Sep 1, 2016 9:19 AM, "Frantz FISCHER" <ffisc...@nuxeo.com> wrote:

> Hello,
>
>
> 2.7.10 python own release.
>
> Got another crash mentioning the _pylite3.pyd module as you suspected
> before.
>
>
> Thanks,
>
> Frantz.
>
>
> On 01/09/2016 17:00, Scott Robison wrote:
>
>> Specifically, what version / distribution of Python 2 is in use? Python's
>> own release, ActiveState, other?
>>
>> On Sep 1, 2016 8:27 AM, "Frantz FISCHER" <ffisc...@nuxeo.com> wrote:
>>
>> Hello Richard,
>>>
>>>
>>> Thank you for your answer!
>>>
>>> Could you please tell me which details you would require?
>>>
>>> I managed to get a windows mini dump, would that be of any help?
>>>
>>> This is the default sqlite3.dll bundled with python 2.7. Do you require
>>> the python module? the dll itself?
>>>
>>>
>>> Regards,
>>>
>>> Frantz.
>>>
>>>
>>> On 01/09/2016 14:33, Richard Hipp wrote:
>>>
>>> On 9/1/16, Frantz FISCHER<ffisc...@nuxeo.com>  wrote:
>>>>
>>>> Using our client Python app for synchronization with a server it crashes
>>>>> frequently generating c005 error and also mentioning sqlite3.dll.
>>>>>
>>>>> Usually when an application crashes and SQLite is mentioned in the
>>>> stack trace, that indicates that some other module corrupted the heap
>>>> and SQLite was the first unlucky module to stumble over the
>>>> corruption.
>>>>
>>>> That said, if you can provide more details, we will look into the
>>>> matter.
>>>>
>>>>
>>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
>
> *Frantz Fischer*
> Premium Support Team
> +33 (0)1 40 33 79 87
> +33 (0)1 40 33 71 41 (Fax)
> www.nuxeo.com - Content Management Platform <https://www.nuxeo.com>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] client app crashes frequently and points sqlite DLL

[Scott Robison]

Specifically, what version / distribution of Python 2 is in use? Python's
own release, ActiveState, other?

On Sep 1, 2016 8:27 AM, "Frantz FISCHER"  wrote:

> Hello Richard,
>
>
> Thank you for your answer!
>
> Could you please tell me which details you would require?
>
> I managed to get a windows mini dump, would that be of any help?
>
> This is the default sqlite3.dll bundled with python 2.7. Do you require
> the python module? the dll itself?
>
>
> Regards,
>
> Frantz.
>
>
> On 01/09/2016 14:33, Richard Hipp wrote:
>
>> On 9/1/16, Frantz FISCHER  wrote:
>>
>>> Using our client Python app for synchronization with a server it crashes
>>> frequently generating c005 error and also mentioning sqlite3.dll.
>>>
>> Usually when an application crashes and SQLite is mentioned in the
>> stack trace, that indicates that some other module corrupted the heap
>> and SQLite was the first unlucky module to stumble over the
>> corruption.
>>
>> That said, if you can provide more details, we will look into the matter.
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Scott Robison]

On Aug 31, 2016 5:29 AM, "sanhua.zh"  wrote:
>
> BTW, what do you think if I mapseparatly instead of the whole db file, ...

I suspect that it wouldn't really help you much, if any.

One, there is overhead in making that many system calls to map a bunch of 4
MiB buffers.

Two, once you've mapped that many buffers, you now have to determine where
each part of your file is in memory before accessing it. You've lost the
benefit of easy address calculations and now have to perform an indirect
lookup, first finding the correct page, next computing the offset into that
page. It would be potentially worse if a piece of data every spanned two
pages, but I doubt that would be an issue for SQLite.

Three, I have no idea what limits exist for mapping pages at the OS level
per process, but it wouldn't surprise me if maybe this exceeded something
like that.

Four, the one potential benefit is that mapping the pages would avoid the
penalty of copying read data from a kernel buffer to your user space
buffer. I think the additional complexity and my other reasons above make
it a less than ideal solution.

If your problem space requires higher speed access to the data than SQLite
is capable of delivering, it seems to me that you'd be better off with a
data storage solution tailored to your requirements. I don't make that
suggestion lightly, and would have to be really desperate for performance
to do it myself, but a more specialized solution can gain performance at
the expense of not being as generally useful. Even then, it might be
difficult to improve on SQLite, a very optimized library for data storage,
manipulation, and access.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite importing csv

[Scott Robison]

On Aug 24, 2016 10:25 PM, "Alan"  wrote:
>
> forgot to reply to one of the questions.
>
> I am using version 3.13.0.0 and it is 64 bit
>
> I am running Windows 10 Pro
>
> computer is intel I3  3.5Ghz with 8Gb RAM.
>
> As my friend has no problem loading the same csv file with his Windows7
computer
>
> I am guessing that it may be to do with running Windows 10.

What directory are you in? Are you using an elevated command prompt?
___
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)

[Scott Robison]

On Aug 24, 2016 5:35 AM, "Simon Slavin"  wrote:
>
>
> On 24 Aug 2016, at 12:33pm, Drago, William @ CSG - NARDA-MITEQ
 wrote:
>
> > This is also the list for System.Data.SQLite. System.Data.SQLite
supports design time components for VS2015, so this may be the correct list
for questions regarding issues with using SQLite and Visual Studio's server
explorer.
>
> Sort-of agreed, but the bugs here are definitely not bugs in
System.Data.SQLite, which can do both of those underlying tasks without
error.  So the bugs must be elsewhere.

That seems reasonable at first blush, but I don't know what level of
cooperation is required from System.Data.SQLite to accommodate this
functionality. You could be right, but given past similar emails, I doubt
either of us is.

I'm sure the responsible party will see this and reply shortly with
something definitive.

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