Re: [sqlite] sqlite-users Digest, Vol 114, Issue 23

[Paul J. McMillan, Sr.]

Thanks Chris!  I'm using .Net 4.6 (VS 2017), which I don't think works with 
System.Data.SQLite yet, so I downgraded to .net 4.5.2.  Seems to be working 
fine.

Please tell me about your classes.  I'm using EF6.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of sqlite-users-requ...@mailinglists.sqlite.org
Sent: Friday, June 23, 2017 8:00 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: sqlite-users Digest, Vol 114, Issue 23

Send sqlite-users mailing list submissions to
sqlite-users@mailinglists.sqlite.org

To subscribe or unsubscribe via the World Wide Web, visit
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
sqlite-users-requ...@mailinglists.sqlite.org

You can reach the person managing the list at
sqlite-users-ow...@mailinglists.sqlite.org

When replying, please edit your Subject line so it is more specific than "Re: 
Contents of sqlite-users digest..."


Today's Topics:

   1. 3 fixes for 3.19.3 (Danny Couture)
   2. Could not load file or assembly error (Paul J. McMillan, Sr.)
   3. User Authentication Extension is broken in 3.19 branch
  (Thien, Christoph)
   4. Re: UTF8-BOM not disregarded in CSV import (Clemens Ladisch)
   5. Re: Could not load file or assembly error (Chris Locke)


--

Message: 1
Date: Thu, 22 Jun 2017 13:30:46 -0400
From: Danny Couture 
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] 3 fixes for 3.19.3
Message-ID:

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

[Cezary H. Noweta]

Hello,

On 2017-06-23 22:12, Mahmoud Al-Qudsi wrote:

I think you and I are on the same page here, Clemens? I abhor the
BOM, but the question is whether or not SQLite will cater to the fact
that the bigger names in the industry appear hell-bent on shoving it
in users’ documents by default.



Given that ‘.import’ and ‘.mode csv’ are “user mode” commands,
perhaps leeway can be shown in breaking with standards for the sake
of compatibility and sanity?


IMHO, this is not a good way to show a leeway. The Unicode Standard has
enough bad things in itself. It is not necessary to transform a good
Unicode's thing into a bad one.

Should SQLite disregard one  sequence, or all 
sequences, or at most 2, 3, 10 ones at the beginning of a file? Such
stream can be produced by a sequence of conversions done by a mix of
conforming and ``breaking the standard for the sake of compatibility''
converters.

To be clear: I understand your point very well - ``let's ignore optional
BOM at the beginning'', but I want to show that there is no limit in
such thinking. Why one optional? You have not pointed out what
compatibility with. The next step is to ignore N BOMs for the sake of
compatibility with breaking the standard for the sake of compatibility
with breaking the standard for the sake of... lim = \infty. I cannot see
any sanity here.

The standard says: ``Only UTF-16/32 (even not UTF-16/32LE/BE) encoding
forms can contain BOM''. Let's conform to this.

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.

-- best regards

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

Re: [sqlite] operator precedence

[Simon Slavin]

On 25 Jun 2017, at 7:59pm, J Decker  wrote:

> SQL Standard
> http://web.cecs.pdx.edu/~len/sql1999.pdf
> doesn't seem to have math operators... and the precedence tables are
> described in longhand without a simple table
> DateTimes have math operators
> 
> "Operations on numbers are performed according to the normal rules of
> arithmetic, within implementation-defined limits, except as provided for in
> Subclause 6.26, ‘‘’’."

Which comes from SQL-92 which comes from SQL-89, almost unchanged.  However, & 
and | were not considered numeric operators at that point, so they didn’t 
figure into statements about the precedence of numeric operators.

BIDMAS (excuse me, I’m British) does apply, and it’s implemented in SQLite.  Of 
course, bitwise operators don’t feature in it because BIDMAS is for numbers and 
not bitwise operators.

My personal answer to the original question is "history".  C wasn’t the huge 
juggernaut back in 1989 that it is now.  A programmer could work their whole 
life without seeing a line of C code.  So there was no instinct to build C 
rules into everything else.

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

Re: [sqlite] operator precedence

[J Decker]

Mysql has bitwise & before |

https://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html

TSQL (MSSQL) has them equal.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql

Oracle doesn't have bitwise & and |
https://docs.oracle.com/cd/A87860_01/doc/server.817/a85397/operator.htm

SQL Standard
http://web.cecs.pdx.edu/~len/sql1999.pdf
doesn't seem to have math operators... and the precedence tables are
described in longhand without a simple table
DateTimes have math operators

"Operations on numbers are performed according to the normal rules of
arithmetic, within implementation-defined limits, except as provided for in
Subclause 6.26, ‘‘’’."

'normal rules of arithmetic' how vague

https://en.wikipedia.org/wiki/Order_of_operations#Programming_languages
(a separate section on rules of programming langauges)
also on this page ...

   - In the United States, the acronym *PEMDAS* is common. It stands for *P*
   arentheses, *E*xponents, *M*ultiplication, *D*ivision, *A*ddition, *S*
   ubtraction.

"Most common in the UK, India and Australia[11]
 are
*BODMAS* meaning "B"rackets, "O"f or "O"rder, "D"ivision, "M"ultiplication,
"A"ddition and "S"ubtraction. Nigeria and some other West African countries
also use *BODMAS*. Similarly in the UK, *BIDMAS* is used, standing for
"B"rackets, "I"ndices, "D"ivision, "M"ultiplication, "A"ddition and
"S"ubtraction."

which doesn't account for & or | operations... so...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] operator precedence

[J Decker]

On Sun, Jun 25, 2017 at 11:11 AM, x  wrote:

>
> Is there a reason why sqlite doesn’t follow the c convention?
>
> e.g. & and | have equal precedence in sqlite.
>

I'd blame it on Postgresql operator precedence;  but then it also doesn't
seem to have bitwise & and | just logical.

https://www.postgresql.org/docs/devel/static/sql-syntax-lexical.html#sql-precedence

http://en.cppreference.com/w/c/language/operator_precedence


> ___
> 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] operator precedence

[Clemens Ladisch]

x wrote:
> Is there a reason why sqlite doesn’t follow the c convention?

Yes.  That reason is named "ISO/IEC 9075", but commonly called
"the SQL standard".


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

[sqlite] operator precedence

[x]

Is there a reason why sqlite doesn’t follow the c convention?

e.g. & and | have equal precedence in sqlite.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Providing incrementing column to query

[Jean-Luc Hainaut]

Some suggestions, the validity of which depend on the context and 
whether some ordering must be preserved.
Let T(C1 primary key,other_data) be the table with which we want to 
associate a sequence number (as pseudo column Seq).


Technique 1: Computing Seq through a recursive CTE.

create table T0 as
with SEQUENCE(Seq,C1,other_data) as
 (select 1,C1,other_data
  from (select C1,other_data
from   T order by C1 limit 1)
   union
select S.Seq+1,T.C1,T.other_data
from   T, SEQUENCES
where  T.C1 = (select min(C1)
   from   T
   where  C1 > S.C1)
  )
select * from SEQUENCE;

Technique 2: Extracting rowid from source table (unordered)

create temp table T1(Seq integer,C1,other_data);
insert into T1
   select rowid as Seq,* from T order by C1;

Technique 3: Extracting rowid from a temp table (ordered)

create temp table T2as
   select 0 as Seq,* from T order by C1;
update T2 set Seq = rowid;

Technique 4: Adding auto incremented column in a temp table

create temp table T3(Seq integer primary key autoincrement,C1,other_data)
insert into T3(C1,other_data) select * from Torder by C1;

Technique 5: From declarative definition - suggestion of (C. Ladish)

create temp table T4as
select (select count(*)
from   TT2
where  T2.C1 <= T1.C1) as Seq,
C1,
other_data
from T T1 order by C1;

For small tables (10-100), the execution times are similar (around 1 
ms.), but for medium-size to large tables, some techniques are better.

For a source table T of 32,000 rows of 30 bytes, there are clear winners:
  - technique 1: 185 ms.
- technique 2: 24 ms.
- technique 3: 58 ms.
- technique 4: 17 ms.
- technique 5: 42,000 ms.

All the scores are linear wrt table size but the declarative one, which 
is quadratic.


Regards

Jean-Luc Hainaut





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

Re: [sqlite] JSON1 extension / json_patch / no such function

[Robert M. Münch]

On 25 Jun 2017, at 10:36, David Empson wrote:

> json_patch() was added to the json1 extension in SQLite 3.18.0, but the other 
> functions were there in earlier versions as far back as SQLite 3.9.0.

Hi, I see...

> Looking at https://sqlitestudio.pl/index.rvt?act=changelog it appears the 
> latest version of SQLiteStudio (3.1.1) is using SQLite 3.15.0, so that is 
> consistent with it not supporting json_patch().

Ok, thanks.

> As for your own program, are you compiling the SQLite 3.18.0 (or later) 
> amalgamation and linking it directly into your program,

Yes, I do this. I'm using 3.19.3 now.

I found out that if you don't set SQLITE_ENABLE_JSON1 it seems some functions 
are available but not all... when setting SQLITE_ENABLE_JSON1 all are 
available. That's a bit strange IMO, but might be a problem on my end, not sure.


-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JSON1 extension / json_patch / no such function

[David Empson]

json_patch() was added to the json1 extension in SQLite 3.18.0, but the other 
functions were there in earlier versions as far back as SQLite 3.9.0. Looking 
at https://sqlitestudio.pl/index.rvt?act=changelog it appears the latest 
version of SQLiteStudio (3.1.1) is using SQLite 3.15.0, so that is consistent 
with it not supporting json_patch().

As for your own program, are you compiling the SQLite 3.18.0 (or later) 
amalgamation and linking it directly into your program, or are you using a 
version of SQLite which is supplied by your OS or a library, which might be an 
older version?

You can check which version of SQLite your program is using with:

SELECT sqlite_version()

> On 25/06/2017, at 8:21 PM, Robert M. Münch  
> wrote:
> 
> Hi, I'm trying to use the json_patch function in a query and tried in 
> SQLiteStudio and my own program and both times I get a "Error: Database no 
> such function: json_patch"
> 
> The query I want to do is:
> 
> UPDATE json_products SET json_value = json_patch(json_value,'{"col-1":1000}')
> 
> json_extract works and json_value too. So I'm really wondering why json_patch 
> is missing. I checked the sources, and the code is there.
> 
> I really don't have an idea what's up here.


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

[sqlite] JSON1 extension / json_patch / no such function

[Robert M. Münch]

Hi, I'm trying to use the json_patch function in a query and tried in 
SQLiteStudio and my own program and both times I get a "Error: Database no such 
function: json_patch"

The query I want to do is:

UPDATE json_products SET json_value = json_patch(json_value,'{"col-1":1000}')

json_extract works and json_value too. So I'm really wondering why json_patch 
is missing. I checked the sources, and the code is there.

I really don't have an idea what's up here.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users