Re: [sqlite] Why this query plan?

[Hick Gunter]

Since you did not specify an ORDER BY clause, SQLite is free to return rows in 
*any* order. The order may even change if the underlying schema changes and 
SQLite finds a better way to compute the results.

A "covering index" is one that contains all the fields required from a certain 
table to fulfill the request. Retrieving fields from the covering index avoids 
having to access the complete row from the table and is thus very much faster 
(no second disk access, no unpacking of the retrieved row).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Bart Smissaert
Gesendet: Donnerstag, 12. Jänner 2017 01:40
An: General Discussion of SQLite Database 
Betreff: [sqlite] Why this query plan?

Say I have a table created like this:

create table table1(
[id] integer primary key,
[dob] integer)

with an index (not unique) on dob

and I run this SQL:

select id, dob from table1

then the query plan I get is:

SCAN TABLE TABLE1 USING COVERING INDEX IDX_TABLE1_DOB

The result is that the output is descending on DOB.
I expected and preferred if the output was ascending on rowid.
What is the idea/logic of this query plan?

Using SQLite 3.16.2


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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Why this query plan?

[Bart Smissaert]

I am fully aware of this and I have no problem at all with this behaviour.
I just wondered why it choose the particular plan in this situation.
There is no criticism, just curiosity.

RBS

On Thu, Jan 12, 2017 at 12:45 AM, Richard Hipp  wrote:

> On 1/11/17, Bart Smissaert  wrote:
> >
> > The result is that the output is descending on DOB.
> > I expected and preferred if the output was ascending on rowid.
>
> If you omit the ORDER BY clause, then the SQL database engine (*any*
> engine, not just SQLite) is free to return the rows in whatever random
> order it chooses.  And it does not need to explain itself when it
> does.  :-)
>
> If you expect and/or prefer the output to be in order of ascending
> rowid, then add
>
>  ORDER BY rowid ASC
>
> to the end of the query.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert into with Id...

[hfiandor]

Dear list members:

 

I’m trying to read a .csv file and introduce in an SQLite table, using the
insert into command.

 

In the .csv file I have not used this Id field (it was defined as integer
and autoincrease).

 

I will appreciate any help about how to copy from a .csv file to an SQLite
table.

 

Thanks in advance,

 

Ing. Héctor F. Fiandor Rosario

 

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

[sqlite] Why SQLite Use Different Malloc Zone While CPU Count > 1?

[sanhua.zh]

Following the code in mem1.c, Why SQLite Use Different Malloc Zone While CPU 
Count  1?


static int sqlite3MemInit(void *NotUsed){
#if defined(__APPLE__)  !defined(SQLITE_WITHOUT_ZONEMALLOC)
 int cpuCount;
 size_t len;
 if( _sqliteZone_ ){
  return SQLITE_OK;
 }
 len = sizeof(cpuCount);
 /* One usually wants to use hw.acctivecpu for MT decisions, but not here */
 sysctlbyname("hw.ncpu", cpuCount, len, NULL, 0);
 if( cpuCount1 ){
  /* defer MT decisions to system malloc */
  _sqliteZone_ = malloc_default_zone();
 }else{
  /* only 1 core, use our own zone to contention over global locks,
  ** e.g. we have our own dedicated locks */
  bool success;
  malloc_zone_t* newzone = malloc_create_zone(4096, 0);
  malloc_set_zone_name(newzone, "Sqlite_Heap");
  do{
   success = OSAtomicCompareAndSwapPtrBarrier(NULL, newzone,
(void * volatile *)_sqliteZone_);
  }while(!_sqliteZone_);
  if( !success ){
   /* somebody registered a zone first */
   malloc_destroy_zone(newzone);
  }
 }
#endif
 UNUSED_PARAMETER(NotUsed);
 return SQLITE_OK;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] extension to run bash

[Warren Young]

On Jan 11, 2017, at 4:01 PM, Jim Callahan  
wrote:
> 
>> How much doing all that is worth is a different question, since the calls
> made through this
>> proposed system() SQLite function would also likely be non-portable.  In
> this very example,
>> there is no wc on Windows.
> 
> I would suggest renaming the proposed system() function bash()

Definitely not.  Bash is not the only shell on POSIX systems, and system() or 
popen() on such systems may or may not be calling Bash to do their work.

You also have the Windows option I brought up in my previous post; you wouldn’t 
want to have to write a separate function called cmd() that only worked in 
SQLite SQL when run on Windows.

I would suggest not calling it system().  C’s system() doesn’t work like the 
proposed SQLite extension: you don’t get a way to capture the output.  Calling 
it popen() also doesn’t seem right.

Something like cmdsubst() seems more accurate.  (Named after the POSIX “command 
substitution” feature, which is what this proposed function would actually 
provide.)

> now and in the future there may be different command line shells.

Sure, but naming the function bash() implies that the function would have to be 
changed somehow to operate with these other shells.  That’s not how POSIX 
systems work: system() and popen() on POSIX systems use an 
implementation-defined POSIX shell, which may or may not be Bash.

You shouldn’t care what shell system() and popen() use underneath.  As long as 
you give it a command in valid POSIX shell syntax, it should run correctly 
everywhere the referenced external commands exist.

> The (now) new Windows 10 Anniversary Edition has the option of installing a
> shell that nearly runs Canonical Ubuntu Linux's BASH shell.

There are severe limitations to depending on that besides the fact that it 
isn’t installed by default.

While you can run “bash -c 'some command'” from a native Windows executable and 
capture its output, what happens inside the Bash environment under WSL is 
largely firewalled off from the regular Windows environment except for I/O 
interactions.  (i.e. disk files, network traffic, etc.)

So for example, you couldn’t use WSL’s bash.exe via this SQLite extension to 
detect whether notepad.exe is running:

cmdsubst('pgrep notepad.exe')

WSL has a separate process table from the native Windows one, so the pgrep 
running under WSL can’t find notepad.exe, even if it is running.

> In a few years
> it will likely be a routine part of Windows.

I doubt it.  You have to turn off a bunch of security mechanisms in Windows to 
get it to run.  (So-called “developer mode.”)

Unless they find a way to allow WSL to run without needing developer mode 
privileges, installing WSL by default would effectively roll back all those 
protections for everyone.

> Thus, at some point, Linux, OS/X and Windows will all support Bash scripts.

See, there’s where talking about “Bash” gets you into trouble.

I’ve got something like 10 Linux boxes in this room here with me that don’t 
have Bash on them.  They run Busybox[1] instead; its “sh” implementation is 
based on the Almquist shell.[2]


[1] https://busybox.net/
[2] https://en.wikipedia.org/wiki/Almquist_shell

> For now, there are non-native emulators MinGW/MSys and Cygwin to provide
> Bash on Windows.

MSYS and Cygwin are neither non-native nor “emulators.”  Executables produced 
by the MSYS and Cygwin GCC compilers are, in fact, more “native” than WSL 
executables.

WSL runs Linux ELF executables and implements the Linux kernel syscall 
interface.  Cygwin runs Windows PE executables and implements a POSIX/Linux 
interface in terms of the native Windows API.  MSYS is the same, being a fork 
of Cygwin.

> Cygwin
> http://www.mingw.org/node/21

That’s a very one-sided argument.

Cygwin applications can call into the native Windows API.  It is bad form to do 
something via the native Windows API that Cygwin already provides, as there may 
be cross-app interop considerations when you bypass the POSIX API that Cygwin 
provides, but there is nothing actually stopping you from doing that.

In fact, one improvement made to SQLite a few years ago was to switch it from 
using native Windows file locking when built under Cygwin to use POSIX or BSD 
locking mechanisms, so that two programs built under Cygwin that both used 
SQLite would get the advisory locking semantics they expect, not the mandatory 
locking semantics Windows gives you by default.  (It’s more complicated than 
that, but I don’t want to go deeper into it here.)

Instead of letting the MinGW people tell you what Cygwin is, how about you let 
the Cygwin project speak for themselves:

   https://cygwin.com/cygwin-ug-net/programming.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] extension to run bash

[Roman Fleysher]

I am not even sure myself this is the right path.

I have table with file names and need operations to be performed on columns  
(i.e. on files). Results, numeric or new file names,  are to be recorded in a 
column. I see two ways:

From bash script, make list of rows, run commands, load results to sqlite. Or

From sqlite, use extension to run commands for each row.

Both have issues.

Roman




 Original message 
From: Richard Hipp 
Date: 1/11/17 7:23 PM (GMT-05:00)
To: SQLite mailing list 
Subject: Re: [sqlite] extension to run bash

On 1/11/17, Bob Friesenhahn  wrote:
>
> For anyone thinking that it is a good idea to embed shell
> functionality in the SQL interpreter, it makes the SQL interpreter
> much less secure for untrusted inputs.

Nobody is suggesting that this become a standard feature of the
language.  Roman wants a "loadable extension", a separate shared
library that will only be used by Roman himself, and then under
carefully controlled circumstances.
--
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] LIKE and the like and SIMilarity

[Keith Medcalf]

It can.  You declare the column to have a collation of NOCASE.  In the case of 
the select you gave which does not d=contain any wildcards (and therefore is 
not needful of LIKE) you can pronounce:

SELECT trim(name) FROM names WHERE name = 'Ben' and name = 'benjamin' collate 
nocase


However since it is impossible for a string to equal both 'Ben' and 'benjamin" 
and the same time, even if both comparisons are case and accent insensitive, 
you will always get no rows returned.

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Anony Mous
> Sent: Wednesday, 11 January, 2017 10:55
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] LIKE and the like and SIMilarity
> 
> Here's the problem as I see it (demo SQL is lame, but makes the point):
> 
> SELECT trim(name) FROM names WHERE name LIKE('Ben') and name
> ILIKE('benjamin')
> 
> ...you can't do that in SqLite using a pragma, can you? If you can, I'd
> sure like to learn how.
> 
> If you can't, not to belabor the point, but you *can* do it in PostgreSQL,
> and while I'm not suggesting that SqLite should strive for the
> sophistication of PostgreSQL, the issue of SQL programmer ability to use,
> and mix, both case-sensitive and case-insensitive means is pretty basic
> stuff.
> 
> If the SQL spec for LIKE is "collation of characters", fine, by all means
> implement the capability another way that uses more reasonable means. I
> don't care what it is called at *all*.
> 
> The lack of the *ability* really can't be defended. It's down to "how to
> do
> it", not "why do it."
> 
> Textual data has case. Sometimes that matters. Sometimes it doesn't. A
> database engine should be able to cleanly deal with that without forcing
> the programmer to write custom code.
> 
> --Ben
> fyng...@gmail.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] Why this query plan?

[Richard Hipp]

On 1/11/17, Simon Slavin  wrote:
>
> On 12 Jan 2017, at 12:49am, Richard Hipp  wrote:
>
>> For years I have threatened to make it a feature of SQLite that it
>> really does output the rows in some random order if you omit the ORDER
>> BY clause
>
> SQLITE_CONFIG_RANDOMIZEWHATYOUCAN_LOL
>

The "PRAGMA reverse_unordered_selects=ON" statement has long been
available to do this.  But it is an optional feature that has to be
turned on.  And I don't think anybody ever turns it on.  My proposal
is to make it random.

Maybe it would be sufficient to initialize the
reverse_unordered_selects setting to a random value (on or off) inside
of sqlite3_open().

-- 
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

Re: [sqlite] Why this query plan?

[Simon Slavin]

On 12 Jan 2017, at 12:45am, Richard Hipp  wrote:

> On 1/11/17, Bart Smissaert  wrote:
>> 
>> The result is that the output is descending on DOB.
>> I expected and preferred if the output was ascending on rowid.
> 
> If you omit the ORDER BY clause, then the SQL database engine (*any*
> engine, not just SQLite) is free to return the rows in whatever random
> order it chooses.  And it does not need to explain itself when it
> does.  :-)

Not only that, but SQLite may return them in one order one day, and another 
order a different day, without you changing a single line of your program or 
recompiling it.  You may use the command-line tool to add another index to the 
database and it may decide to use that one.  Or you may run ANALYZE.

So just because you’ve seen it decide to use IDX_TABLE1_DOB while you’re 
developing your program, don’t depend on it doing the same thing on your 
customer’s site.  As Richard wrote, if you want something specific, ask for it 
specifically.

On 12 Jan 2017, at 12:49am, Richard Hipp  wrote:

> For years I have threatened to make it a feature of SQLite that it
> really does output the rows in some random order if you omit the ORDER
> BY clause

SQLITE_CONFIG_RANDOMIZEWHATYOUCAN_LOL

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

Re: [sqlite] Why this query plan?

[Richard Hipp]

On 1/11/17, Richard Hipp  wrote:
>
> If you omit the ORDER BY clause, then the SQL database engine (*any*
> engine, not just SQLite) is free to return the rows in whatever random
> order it chooses.  And it does not need to explain itself when it
> does.  :-)
>

For years I have threatened to make it a feature of SQLite that it
really does output the rows in some random order if you omit the ORDER
BY clause - specifically to expose the common bug of omitting the
ORDER BY clause when the order matters.  I have so far resisted that
impulse because it makes testing harder.  But this email thread makes
me think that such a feature really would be worth the trouble of
adding.

-- 
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

Re: [sqlite] Why this query plan?

[Richard Hipp]

On 1/11/17, Bart Smissaert  wrote:
>
> The result is that the output is descending on DOB.
> I expected and preferred if the output was ascending on rowid.

If you omit the ORDER BY clause, then the SQL database engine (*any*
engine, not just SQLite) is free to return the rows in whatever random
order it chooses.  And it does not need to explain itself when it
does.  :-)

If you expect and/or prefer the output to be in order of ascending
rowid, then add

 ORDER BY rowid ASC

to the end of the query.
-- 
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] Why this query plan?

[Bart Smissaert]

Say I have a table created like this:

create table table1(
[id] integer primary key,
[dob] integer)

with an index (not unique) on dob

and I run this SQL:

select id, dob from table1

then the query plan I get is:

SCAN TABLE TABLE1 USING COVERING INDEX IDX_TABLE1_DOB

The result is that the output is descending on DOB.
I expected and preferred if the output was ascending on rowid.
What is the idea/logic of this query plan?

Using SQLite 3.16.2


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

Re: [sqlite] extension to run bash

[Richard Hipp]

On 1/11/17, Bob Friesenhahn  wrote:
>
> For anyone thinking that it is a good idea to embed shell
> functionality in the SQL interpreter, it makes the SQL interpreter
> much less secure for untrusted inputs.

Nobody is suggesting that this become a standard feature of the
language.  Roman wants a "loadable extension", a separate shared
library that will only be used by Roman himself, and then under
carefully controlled circumstances.
-- 
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

Re: [sqlite] LIKE and the like and SIMilarity

[Jens Alfke]

> On Jan 11, 2017, at 1:34 PM, R Smith  wrote:
> 
> SELECT a FROM t WHERE a = b COLLATE NOCASE;

D’ohh! I overlooked the COLLATE operator. Perfect.

(In my situation, these strings are not coming directly from columns, so 
setting collation on columns doesn’t make sense.)

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

Re: [sqlite] extension to run bash

[Bob Friesenhahn]

On Wed, 11 Jan 2017, Jim Callahan wrote:


How much doing all that is worth is a different question, since the calls

made through this

proposed system() SQLite function would also likely be non-portable.  In

this very example,

there is no wc on Windows.


I would suggest renaming the proposed system() function bash() since now
and in the future there may be different command line shells.


For anyone thinking that it is a good idea to embed shell 
functionality in the SQL interpreter, it makes the SQL interpreter 
much less secure for untrusted inputs.


There are already SQL injection exploit opportunities and now SQL 
injection exploit opportunities also become shell exploit 
opportunities.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Domingo Alvarez Duarte]

Hello again !

I did some changes and got a better plan but it takes for ever, it seems 
that sqlite3 can not merge the where clause before the "group by" scan 
the whole tables:


===

CREATE VIEW despesas_municipio_orgao_list_view AS
SELECT
a."municipio_id" rowid,
a."ano_exercicio",
c.name as municipio,
b.name as orgao,
count(*) count,
sum(vl_despesa) total,
a."municipio_id",
a."orgao_id"
FROM main."despesas_master" AS a
JOIN municipios_orgaos b ON a.orgao_id=b.id
JOIN municipios c ON a.municipio_id=c.id
LEFT JOIN despesas_detalhe d ON a.id=d.despesa_id
JOIN tipos_despesa e ON d.tp_despesa_id=e.id and e.name='Valor Pago'
GROUP BY municipio_id, orgao_id;

explain query plan
select *
from despesas_municipio_orgao_list_view
where municipio_id=2;

===

Output:

===

selectid|order|from|detail
0|0|0|SCAN TABLE despesas_master AS a USING INDEX 
despesas_master_municipio_idx
0|1|1|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY 
(rowid=?)

0|2|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE despesas_detalhe AS d USING INDEX 
despesas_detalhe_despesa_idx (despesa_id=?)

0|4|4|SEARCH TABLE tipos_despesa AS e USING INTEGER PRIMARY KEY (rowid=?)

===

Executing this takes 3.10414 seconds (the result includes municipio_id=2)

===

select *
from despesas_municipio_orgao_list_view

limit 50

===

Executing this takes 111.839 seconds

===

select *
from despesas_municipio_orgao_list_view
where municipio_id=2;

===

Is this expected to be ok ?


On 11/01/17 19:50, Domingo Alvarez Duarte wrote:

Hello Richard !

It seems that sqlite query plan is having trouble with this query too:

===

create table if not exists municipios(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists municipios_orgaos(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists tipos_despesa(
id integer primary key,
name varchar not null unique collate nocase_slna
);

CREATE TABLE if not exists despesas_master(
id integer primary key,
ano_exercicio integer not null,
municipio_id integer not null,
orgao_id integer not null,
unique(municipio_id, orgao_id)
);

CREATE TABLE if not exists despesas_detalhe(
id integer primary key,
despesa_id integer not null,
tp_despesa_id integer not null
);
create index if not exists despesas_detalhe_despesa_idx on 
despesas_detalhe(despesa_id);


select 'expected good plan';
select '===';
explain query plan
select * from (
SELECT
a."municipio_id",
a."ano_exercicio",
c.name as municipio,
b.name as orgao,
--count(*) count,
a."orgao_id"
FROM despesas_master AS a
JOIN despesas_detalhe d ON a.id=d.despesa_id
and d.tp_despesa_id=(select id from tipos_despesa where name='any')
JOIN municipios c ON a.municipio_id=c.id
JOIN municipios_orgaos b ON a.orgao_id=b.id
--GROUP BY municipio_id, orgao_id
) tbl
WHERE municipio_id=2;

select '';
select 'unexpected bad plan';
select '===';
explain query plan
select * from (
SELECT
a.municipio_id,
a.ano_exercicio,
c.name as municipio,
b.name as orgao,
count(*) count,
a.orgao_id
FROM despesas_master AS a
JOIN despesas_detalhe d ON a.id=d.despesa_id
and d.tp_despesa_id=(select id from tipos_despesa where name='any')
JOIN municipios c ON a.municipio_id=c.id
JOIN municipios_orgaos b ON a.orgao_id=b.id
GROUP BY municipio_id, orgao_id
) tbl
WHERE municipio_id=2;

===

sqlite3 < the_above_sql_file

===

expected good plan
===
0|0|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|1|0|SEARCH TABLE despesas_master AS a USING INDEX 
sqlite_autoindex_despesas_master_1 (municipio_id=?)
0|2|3|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY 
(rowid=?)
0|3|1|SEARCH TABLE despesas_detalhe AS d USING INDEX 
despesas_detalhe_despesa_idx (despesa_id=?)

0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE tipos_despesa USING COVERING INDEX 
sqlite_autoindex_tipos_despesa_1 (name=?)


unexpected bad plan
===
0|0|1|SCAN TABLE despesas_detalhe AS d
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE tipos_despesa USING COVERING INDEX 
sqlite_autoindex_tipos_despesa_1 (name=?)
0|1|0|SEARCH TABLE despesas_master AS a USING INTEGER PRIMARY KEY 
(rowid=?)

0|2|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY 
(rowid=?)

0|0|0|USE TEMP B-TREE FOR GROUP BY

===

On 05/01/17 23:16, Richard Hipp wrote:

On 1/5/17, Domingo Alvarez Duarte  wrote:

Hello !

Today I found this unexpected behavior when using sqlite3 trunk:

When using views with joins sqlite3 is choosing expected plans except
for "LEFT JOIN", bellow is the snippet that shows this unexpected 
behavior.


===

create table if not exists a(id integer primary key, val text);
create table if not exists b(id integer primary key, a_id 

Re: [sqlite] extension to run bash

[Jim Callahan]

> How much doing all that is worth is a different question, since the calls
made through this
> proposed system() SQLite function would also likely be non-portable.  In
this very example,
> there is no wc on Windows.

I would suggest renaming the proposed system() function bash() since now
and in the future there may be different command line shells.

The (now) new Windows 10 Anniversary Edition has the option of installing a
shell that nearly runs Canonical Ubuntu Linux's BASH shell. In a few years
it will likely be a routine part of Windows.

See:
https://msdn.microsoft.com/en-us/commandline/wsl/about

Thus, at some point, Linux, OS/X and Windows will all support Bash scripts.

For now, there are non-native emulators MinGW/MSys and Cygwin to provide
Bash on Windows.

MinGW/MSys
http://www.mingw.org/wiki/msys

Cygwin
http://www.mingw.org/node/21

Jim Callahan
Orlando, FL



On Wed, Jan 11, 2017 at 5:21 PM, Warren Young  wrote:

> On Jan 11, 2017, at 3:11 PM, Richard Hipp  wrote:
> >
> > On 1/11/17, Scott Hess  wrote:
> >>  UPDATE result SET nRows = system('wc', '-l', fileNames);
> >>
> >> ...
> >> [Though, yes, this means you'll have to use fork() and execlp() and
> >> waitpid() to implement, rather than popen().
> >
> > Which further means that the code would not be portable to Windows.
>
> There is a way to do popen() in Windows, but it’s reeealy ugly:
>
>https://msdn.microsoft.com/en-us/library/ms682499%28VS.85%29.aspx
>
> How much doing all that is worth is a different question, since the calls
> made through this proposed system() SQLite function would also likely be
> non-portable.  In this very example, there is no wc on Windows.
>
> This is essentially the same problem that leads to autoconf, autosetup,
> CMake, etc: you can’t write a portable Makefile, if by “portable” you
> include non-POSIX OSes.
> ___
> 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] extension to run bash

[Warren Young]

On Jan 11, 2017, at 3:11 PM, Richard Hipp  wrote:
> 
> On 1/11/17, Scott Hess  wrote:
>>  UPDATE result SET nRows = system('wc', '-l', fileNames);
>> 
>> ...
>> [Though, yes, this means you'll have to use fork() and execlp() and
>> waitpid() to implement, rather than popen().
> 
> Which further means that the code would not be portable to Windows.

There is a way to do popen() in Windows, but it’s reeealy ugly:

   https://msdn.microsoft.com/en-us/library/ms682499%28VS.85%29.aspx

How much doing all that is worth is a different question, since the calls made 
through this proposed system() SQLite function would also likely be 
non-portable.  In this very example, there is no wc on Windows.

This is essentially the same problem that leads to autoconf, autosetup, CMake, 
etc: you can’t write a portable Makefile, if by “portable” you include 
non-POSIX OSes.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Now memory usage for update of big tables works fine

[Domingo Alvarez Duarte]

Hello Richard !

I just tested the latest sqlite3 with this "Changes to allow some 
multi-row UPDATE statements to avoid the two-pass approach." and before 
it was blowing up my machine (swapping) and now the memory usage remains 
basically the same as the startup without doing nothing.


Thank you !

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

Re: [sqlite] extension to run bash

[Richard Hipp]

On 1/11/17, Scott Hess  wrote:
> Though it may be cleaner long-term to implement system() to pass
> individual arguments, rather than passing a single string which will
> have to be re-processed by the shell.  So the API would end up like:
>   UPDATE result SET nRows = system('wc', '-l', fileNames);
>
> The reason I suggest this is because [fileNames] could have spaces
> which would have to be escaped, but there are probably a dozen other
> similar issues which are likely to come up.
>
> [Though, yes, this means you'll have to use fork() and execlp() and
> waitpid() to implement, rather than popen().

Which further means that the code would not be portable to Windows.

> There are examples out
> there of how to do that:
>
> https://www.securecoding.cert.org/confluence/pages/viewpage.action?pageId=2130132
> ]
>
> -scott
>
> On Wed, Jan 11, 2017 at 1:38 PM, Roman Fleysher
>  wrote:
>> Yes, Richard, this is exactly what I mean.
>>
>> Roman
>> 
>> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on
>> behalf of Richard Hipp [d...@sqlite.org]
>> Sent: Wednesday, January 11, 2017 4:34 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] extension to run bash
>>
>> On 1/11/17, Roman Fleysher  wrote:
>>> Dear SQLites,
>>>
>>> I am using exclusively sqlite3 shell for all the processing and may need
>>> ability to run bash commands and assign result to a column. For example:
>>>
>>> UPDATE  result SET nRows =` wc -l fileNames` ;
>>>
>>> Here I used `` as would be in bash for command substitution. This would
>>> run
>>> wc command (word count), count number of lines in each file listed in
>>> column
>>> fileNames and update the row correspondingly.
>>>
>>> As far as I understand I should be able to write loadable extension to
>>> accomplish this.
>>
>> No, You cannot do exactly what you describe with a loadable extension.
>>
>> But you could, perhaps, create a loadable extension that implements a
>> new system() SQL function like this:
>>
>>UPDATE result SET nRows = system('wc -l ' || fileNames);
>>
>> Note that || is the SQL string concatenation operator.  You didn't
>> say, but I'm guessing that fileNames is a column in the result table.
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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

Re: [sqlite] extension to run bash

[Scott Hess]

Though it may be cleaner long-term to implement system() to pass
individual arguments, rather than passing a single string which will
have to be re-processed by the shell.  So the API would end up like:
  UPDATE result SET nRows = system('wc', '-l', fileNames);

The reason I suggest this is because [fileNames] could have spaces
which would have to be escaped, but there are probably a dozen other
similar issues which are likely to come up.

[Though, yes, this means you'll have to use fork() and execlp() and
waitpid() to implement, rather than popen().  There are examples out
there of how to do that:
   
https://www.securecoding.cert.org/confluence/pages/viewpage.action?pageId=2130132
]

-scott

On Wed, Jan 11, 2017 at 1:38 PM, Roman Fleysher
 wrote:
> Yes, Richard, this is exactly what I mean.
>
> Roman
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 11, 2017 4:34 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] extension to run bash
>
> On 1/11/17, Roman Fleysher  wrote:
>> Dear SQLites,
>>
>> I am using exclusively sqlite3 shell for all the processing and may need
>> ability to run bash commands and assign result to a column. For example:
>>
>> UPDATE  result SET nRows =` wc -l fileNames` ;
>>
>> Here I used `` as would be in bash for command substitution. This would run
>> wc command (word count), count number of lines in each file listed in column
>> fileNames and update the row correspondingly.
>>
>> As far as I understand I should be able to write loadable extension to
>> accomplish this.
>
> No, You cannot do exactly what you describe with a loadable extension.
>
> But you could, perhaps, create a loadable extension that implements a
> new system() SQL function like this:
>
>UPDATE result SET nRows = system('wc -l ' || fileNames);
>
> Note that || is the SQL string concatenation operator.  You didn't
> say, but I'm guessing that fileNames is a column in the result table.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] extension to run bash

[Roman Fleysher]

I know it is not magic. It is SQLite developers' brain and effort. But it looks 
like magic to me.

Yes, this is example I see how I could use.

I am still thinking if this is what I really need. I am working with images and 
need, among other things, some summary measures to be placed into tables for 
analysis.

Thank you,

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 11, 2017 4:44 PM
To: SQLite mailing list
Subject: Re: [sqlite] extension to run bash

On 1/11/17, Roman Fleysher  wrote:
> Yes, Richard, this is exactly what I mean.
>

Then maybe use the https://www.sqlite.org/src/file/ext/misc/rot13.c
extension as a prototype from which to develop yours.

--
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] Bad query plan selection only with "LEFT JOIN"

[Domingo Alvarez Duarte]

Hello Richard !

It seems that sqlite query plan is having trouble with this query too:

===

create table if not exists municipios(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists municipios_orgaos(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists tipos_despesa(
id integer primary key,
name varchar not null unique collate nocase_slna
);

CREATE TABLE if not exists despesas_master(
id integer primary key,
ano_exercicio integer not null,
municipio_id integer not null,
orgao_id integer not null,
unique(municipio_id, orgao_id)
);

CREATE TABLE if not exists despesas_detalhe(
id integer primary key,
despesa_id integer not null,
tp_despesa_id integer not null
);
create index if not exists despesas_detalhe_despesa_idx on 
despesas_detalhe(despesa_id);


select 'expected good plan';
select '===';
explain query plan
select * from (
SELECT
a."municipio_id",
a."ano_exercicio",
c.name as municipio,
b.name as orgao,
--count(*) count,
a."orgao_id"
FROM despesas_master AS a
JOIN despesas_detalhe d ON a.id=d.despesa_id
and d.tp_despesa_id=(select id from tipos_despesa where name='any')
JOIN municipios c ON a.municipio_id=c.id
JOIN municipios_orgaos b ON a.orgao_id=b.id
--GROUP BY municipio_id, orgao_id
) tbl
WHERE municipio_id=2;

select '';
select 'unexpected bad plan';
select '===';
explain query plan
select * from (
SELECT
a.municipio_id,
a.ano_exercicio,
c.name as municipio,
b.name as orgao,
count(*) count,
a.orgao_id
FROM despesas_master AS a
JOIN despesas_detalhe d ON a.id=d.despesa_id
and d.tp_despesa_id=(select id from tipos_despesa where name='any')
JOIN municipios c ON a.municipio_id=c.id
JOIN municipios_orgaos b ON a.orgao_id=b.id
GROUP BY municipio_id, orgao_id
) tbl
WHERE municipio_id=2;

===

sqlite3 < the_above_sql_file

===

expected good plan
===
0|0|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|1|0|SEARCH TABLE despesas_master AS a USING INDEX 
sqlite_autoindex_despesas_master_1 (municipio_id=?)
0|2|3|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY 
(rowid=?)
0|3|1|SEARCH TABLE despesas_detalhe AS d USING INDEX 
despesas_detalhe_despesa_idx (despesa_id=?)

0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE tipos_despesa USING COVERING INDEX 
sqlite_autoindex_tipos_despesa_1 (name=?)


unexpected bad plan
===
0|0|1|SCAN TABLE despesas_detalhe AS d
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE tipos_despesa USING COVERING INDEX 
sqlite_autoindex_tipos_despesa_1 (name=?)

0|1|0|SEARCH TABLE despesas_master AS a USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY 
(rowid=?)

0|0|0|USE TEMP B-TREE FOR GROUP BY

===

On 05/01/17 23:16, Richard Hipp wrote:

On 1/5/17, Domingo Alvarez Duarte  wrote:

Hello !

Today I found this unexpected behavior when using sqlite3 trunk:

When using views with joins sqlite3 is choosing expected plans except
for "LEFT JOIN", bellow is the snippet that shows this unexpected behavior.

===

create table if not exists a(id integer primary key, val text);
create table if not exists b(id integer primary key, a_id integer not
null, val text);
create view if not exists b_view as select b.*, a.* from b left join a
on b.a_id=a.id;
create table if not exists c(id integer primary key, b_id integer not
null, val text);

select 'bad unexpected plan';
explain query plan select c.*, b_view.* from c left join b_view on
c.b_id=b_view.id;

Can you rewrite your query as:

   SELECT *
FROM c LEFT JOIN b ON c.b_id=b.id
 LEFT JOIN a ON b.id=a.id;


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

Re: [sqlite] LIKE and the like and SIMilarity

[Simon Slavin]

On 11 Jan 2017, at 9:34pm, R Smith  wrote:

> Doesn't this already do the trick?
> 
> SELECT a FROM t WHERE a = b COLLATE NOCASE;

Right.  Better still, if you declare the columns as COLLATE NOCASE in the first 
place, the comparison is done ignoring case without you having to state it in 
the SELECT.

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> CREATE TABLE MyTable (nameCS TEXT, nameCI TEXT COLLATE NOCASE);
sqlite> INSERT INTO MyTable VALUES ('Albert','Albert');
sqlite> INSERT INTO MyTable VALUES ('Betty','Betty');
sqlite> SELECT * FROM MyTable WHERE nameCS='betty';
sqlite> SELECT * FROM MyTable WHERE nameCI='betty';
Betty|Betty
sqlite> 

Statements like "I need a case-insensitive comparison function" for a database 
engine worry me.  They make me wonder why you didn’t define your schema 
correctly in the first place.

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

Re: [sqlite] extension to run bash

[Richard Hipp]

On 1/11/17, Roman Fleysher  wrote:
> Yes, Richard, this is exactly what I mean.
>

Then maybe use the https://www.sqlite.org/src/file/ext/misc/rot13.c
extension as a prototype from which to develop yours.

-- 
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

Re: [sqlite] extension to run bash

[Roman Fleysher]

Yes, Richard, this is exactly what I mean.

Roman

From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 11, 2017 4:34 PM
To: SQLite mailing list
Subject: Re: [sqlite] extension to run bash

On 1/11/17, Roman Fleysher  wrote:
> Dear SQLites,
>
> I am using exclusively sqlite3 shell for all the processing and may need
> ability to run bash commands and assign result to a column. For example:
>
> UPDATE  result SET nRows =` wc -l fileNames` ;
>
> Here I used `` as would be in bash for command substitution. This would run
> wc command (word count), count number of lines in each file listed in column
> fileNames and update the row correspondingly.
>
> As far as I understand I should be able to write loadable extension to
> accomplish this.

No, You cannot do exactly what you describe with a loadable extension.

But you could, perhaps, create a loadable extension that implements a
new system() SQL function like this:

   UPDATE result SET nRows = system('wc -l ' || fileNames);

Note that || is the SQL string concatenation operator.  You didn't
say, but I'm guessing that fileNames is a column in the result table.
--
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] extension to run bash

[Jim Callahan]

> may need ability to run bash commands and assign result to a column. For
example:
​> ​
UPDATE  result SET nRows =` wc -l fileNames` ;

​Might be easier to run Bash commands in Bash; write the results to a file​
and then redirect the file into SQLite.

See for example, this Nabble thread.

http://sqlite.1065341.n5.nabble.com/How-accept-sqlite3-commands-from-stdin-td38710.html

Jim Callahan
Orlando, FL




On Wed, Jan 11, 2017 at 4:23 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLites,
>
> I am using exclusively sqlite3 shell for all the processing and may need
> ability to run bash commands and assign result to a column. For example:
>
> UPDATE  result SET nRows =` wc -l fileNames` ;
>
> Here I used `` as would be in bash for command substitution. This would
> run wc command (word count), count number of lines in each file listed in
> column fileNames and update the row correspondingly.
>
> As far as I understand I should be able to write loadable extension to
> accomplish this. My questions are:
>
> 1. Given that I use sqlite3 shell exclusively, does this path makes sense?
> If path should be different, what is it?
> 2. If loadable extension is good way to go, is there an example that I
> could use given that I have zero knowledge of sqlite's internals?
>
> 3. Maybe mixing SQL and shell commands (different syntaxes) is
> linguistically inappropriate and thus difficult?
>
> Thank you,
>
> Roman
>
>
> ___
> 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] extension to run bash

[Richard Hipp]

On 1/11/17, Roman Fleysher  wrote:
> Dear SQLites,
>
> I am using exclusively sqlite3 shell for all the processing and may need
> ability to run bash commands and assign result to a column. For example:
>
> UPDATE  result SET nRows =` wc -l fileNames` ;
>
> Here I used `` as would be in bash for command substitution. This would run
> wc command (word count), count number of lines in each file listed in column
> fileNames and update the row correspondingly.
>
> As far as I understand I should be able to write loadable extension to
> accomplish this.

No, You cannot do exactly what you describe with a loadable extension.

But you could, perhaps, create a loadable extension that implements a
new system() SQL function like this:

   UPDATE result SET nRows = system('wc -l ' || fileNames);

Note that || is the SQL string concatenation operator.  You didn't
say, but I'm guessing that fileNames is a column in the result table.
-- 
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

Re: [sqlite] LIKE and the like and SIMilarity

[R Smith]

On 2017/01/11 11:19 PM, Darren Duncan wrote:

On 2017-01-11 10:11 AM, Jens Alfke wrote:
And while we’re at it, I’d like to see a case-insensitive string 
equality operator.


Yes, that shorthand can be useful.  But don't make it a pragma that 
overrides the meaning of "=", which would be a world of hurt, it needs 
a different name. -- Darren Duncan


Doesn't this already do the trick?

SELECT a FROM t WHERE a = b COLLATE NOCASE;

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

[sqlite] extension to run bash

[Roman Fleysher]

Dear SQLites,

I am using exclusively sqlite3 shell for all the processing and may need 
ability to run bash commands and assign result to a column. For example:

UPDATE  result SET nRows =` wc -l fileNames` ;

Here I used `` as would be in bash for command substitution. This would run wc 
command (word count), count number of lines in each file listed in column 
fileNames and update the row correspondingly.

As far as I understand I should be able to write loadable extension to 
accomplish this. My questions are:

1. Given that I use sqlite3 shell exclusively, does this path makes sense? If 
path should be different, what is it?
2. If loadable extension is good way to go, is there an example that I could 
use given that I have zero knowledge of sqlite's internals?

3. Maybe mixing SQL and shell commands (different syntaxes) is linguistically 
inappropriate and thus difficult?

Thank you,

Roman


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

Re: [sqlite] LIKE and the like and SIMilarity

[Darren Duncan]

On 2017-01-11 10:11 AM, Jens Alfke wrote:

And while we’re at it, I’d like to see a case-insensitive string equality 
operator.


Yes, that shorthand can be useful.  But don't make it a pragma that overrides 
the meaning of "=", which would be a world of hurt, it needs a different name. 
-- Darren Duncan


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

Re: [sqlite] LIKE and the like and SIMilarity

[R Smith]

On 2017/01/11 7:55 PM, Anony Mous wrote:

Here's the problem as I see it (demo SQL is lame, but makes the point):

 SELECT trim(name) FROM names WHERE name LIKE('Ben') and name
ILIKE('benjamin')

...you can't do that in SqLite using a pragma, can you? If you can, I'd
sure like to learn how.


Quite easy actually.
You can make the like be case sensitive or insensitive easily using 
either compile-time options or run-time pragma like:

PRAGMA case_sensitive_like = Off;
(Also check your column collations)

Then, once the LIKE and GLOB works case-sensitive, you can easily 
combine a search with case-insensitive like in this way:
SELECT TRIM(name) FROM names WHERE name LIKE 'Ben%' AND lower(name) LIKE 
'benjamin';

  another possibility is to leave the LIKE case insensitive and do:
SELECT TRIM(name) FROM names WHERE substr(name,3) = 'Ben' AND name LIKE 
'benjamin';


Sorry if it doesn't use the exact same function as PostGres, but it 
definitely CAN be done - there is no "lack of *ability*".





If you can't, not to belabor the point, but you *can* do it in PostgreSQL,
and while I'm not suggesting that SqLite should strive for the
sophistication of PostgreSQL, the issue of SQL programmer ability to use,
and mix, both case-sensitive and case-insensitive means is pretty basic
stuff.


Can PostGres fit on a phone? Can PostGres Save an entire Database in a 
single file? Can PostGres access a DB without a Server?
The "LITE" in SQLite is there for a reason, and it means we do without 
some of the arguably useless syntactic sugars and oversimplifications or 
hand-holdy bits of SQL provided by the large client-server systems. 
(Just to note - I am in no way claiming these functions to be bad or 
unnecessary in the large systems, it's beautiful that they CAN do it, 
but that holds no argument for it to be in SQLite).




Textual data has case. Sometimes that matters. Sometimes it doesn't. A
database engine should be able to cleanly deal with that without forcing
the programmer to write custom code.


As can SQLite. And if you don't like the way SQLite can do it, then you 
are even welcome to add your own user-defined-function to do so, or use 
one of the myriad of off-the-shelf ones already made by other 
enthusiasts - another of the beauties of SQLite - you can compile-in 
almost anything. Can all the other big engines do that? (I'm looking at 
you, SQL Server...)


You can also compile-in many other MATH functions, encryption functions 
and a legion of other additives to suit your specific needs - but please 
don't hate on us simple folk who would rather save the compiled size and 
speed in lieu of a troop of functions we won't need.


(My confidence in saying the above is not because I think the ILIKE 
function is necessarily a bad idea (might even add a very minimum of 
code), but rather based more on the fact that this forum wasn't exactly 
drowning in requests for ILIKE() functions in the last 10 years, so the 
utility increase is dubious - I think this might be the first such a 
request ever, though I could be wrong.)


Cheers,
Ryan

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

Re: [sqlite] Problem with compiled dll on Windows

[Random Coder]

On Wed, Jan 11, 2017 at 10:28 AM, Rael Bauer  wrote:
> 1) Should the howtocompile.html webpage not include this in the "Building A
> Windows DLL" instructions?

Tough question.  Normally, I'd say this is toolset knowledge, and
really doesn't belong in those sort of directions.  But since you're
not the first person to hit this problem, and the directions as
followed result in a DLL that can't be called since it won't export
any functions, even incorrectly, they should probably be updated.

> 2) What is the -Ox flag? I did not find what that was for.

Like most compilers, Microsoft's supports a bunch of optimization
flags.  The SQLite page calls out Os ("favor code space") and O2
("maximize speed").  I use Ox, which is documented as "maximum
optimizations", and in my experience does a decent job at balancing
speed and and code space, but your needs may differ from mine.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with compiled dll on Windows

[Rael Bauer]

@ Random Coder.: Thanks, that was the exact issue. Using a def file 
solved the problem.


1) Should the howtocompile.html webpage not include this in the 
"Building A Windows DLL" instructions?


2) What is the -Ox flag? I did not find what that was for.

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

Re: [sqlite] LIKE and the like and SIMilarity

[Jens Alfke]

> On Jan 11, 2017, at 9:55 AM, Anony Mous  wrote:
> 
> Textual data has case. Sometimes that matters. Sometimes it doesn't. A
> database engine should be able to cleanly deal with that without forcing
> the programmer to write custom code.

+1. And while we’re at it, I’d like to see a case-insensitive string equality 
operator.

>SELECT trim(name) FROM names WHERE name LIKE('Ben') and name
> ILIKE('benjamin')
> 
> ...you can't do that in SqLite using a pragma, can you? If you can, I'd
> sure like to learn how.

But to play devil’s advocate, you _can_ do that with:
 SELECT trim(name) FROM names WHERE name LIKE('Ben') and lower(name) 
LIKE('benjamin’)

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

Re: [sqlite] LIKE and the like and SIMilarity

[Anony Mous]

Here's the problem as I see it (demo SQL is lame, but makes the point):

SELECT trim(name) FROM names WHERE name LIKE('Ben') and name
ILIKE('benjamin')

...you can't do that in SqLite using a pragma, can you? If you can, I'd
sure like to learn how.

If you can't, not to belabor the point, but you *can* do it in PostgreSQL,
and while I'm not suggesting that SqLite should strive for the
sophistication of PostgreSQL, the issue of SQL programmer ability to use,
and mix, both case-sensitive and case-insensitive means is pretty basic
stuff.

If the SQL spec for LIKE is "collation of characters", fine, by all means
implement the capability another way that uses more reasonable means. I
don't care what it is called at *all*.

The lack of the *ability* really can't be defended. It's down to "how to do
it", not "why do it."

Textual data has case. Sometimes that matters. Sometimes it doesn't. A
database engine should be able to cleanly deal with that without forcing
the programmer to write custom code.

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

Re: [sqlite] About ticket c92ecff2ec5f1784 LEFT JOIN problem

[Chris Locke]

Ken,

That went to the mailing list ... to which you are also a recipient of
;)


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

Re: [sqlite] Need help with System.Data.SQLite

[Burtsev , Dmitriy]

I try both "SELECT 1,0;" and "SELECT 1, cast(0 as real)"?
The same error:
Exception calling "WriteToServer" with "1" argument(s): "The given value of 
type SqlDecimal from the data source cannot be converted to type decimal of the 
specified target column."

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Wednesday, January 11, 2017 11:29 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Need help with System.Data.SQLite

What is the result if you exchange your currently executed statement to "SELECT 
1,0;" ? Or to "SELECT 1, cast(0 as real)"?

The effect of declaring a NUMERIC(5,5) field in SQLite is that it's affinity 
becomes NUMERIC. The value .0 thus becomes the integer 0. The other legal 
values (.1 to .9) are all converted to real (double) floating point 
numbers, each to the closest possible approximation (or, if the approximation 
is not "close enough", as text !!!).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Burtsev, Dmitriy
Gesendet: Mittwoch, 11. Jänner 2017 15:48
An: 'SQLite mailing list' 
Betreff: Re: [sqlite] Need help with System.Data.SQLite

I didn't assign any value directly.

This is what I did:

1.   On SQLite create source table.
CREATE TABLE FromNum (
id INT,
Num NUMERIC(5,5) NULL);
INSERT INTO FromNum (id, Num) VALUES (1, .0);

2.  On MS SQL Server 2014 (SP2) create empty destination table.

CREATE TABLE dbo.ToNum (
id INT NOT NULL ,
Num NUMERIC(5,5) NULL);

I can't copy my PowerShell script in this e-mail. Got error "The message's 
content type was not explicitly allowed". I will try pseudocode.

Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll'

Create System.Data.SQLite.SQLiteConnection

Connection CreateCommand()

CommandText = "SELECT * FROM FromNum"

System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader()

Create System.Data.SqlClient.SqlConnection
Create System.Data.SqlClient.SqlBulkCopy SqlConnection, 
[System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
SqlbulkCopy.DestinationTableName = "ToNum"
Sqlbulkcopy.EnableStreaming = true

SqlbulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here

This code is working with NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3) but not 
working with NUMERIC(5,5).

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of GB
Sent: Wednesday, January 11, 2017 2:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] Need help with System.Data.SQLite

SQLite does not really care about precision and scale. See 
https://sqlite.org/datatype3.html for more information.

The culprit lies in the use of SqlDecimal which cannot be used by 
System.Data.SQLite directly and has no implicit conversion to something 
understandable by System.Data.SQLite (for more information about SqlDecimal see 
https://msdn.microsoft.com/en-us/library/xaktx377(v=vs.110).aspx).

However, an explicit conversion exists, so an explicit cast to System.Decimal 
would work. Maybe you could try this at the Point where you assign the Value 
(more information about the explicit operator can be found here: 
https://msdn.microsoft.com/en-us/library/xhbhezf4.aspx).

Also have a close look at what is being fed into the other numeric columns. I 
doubt these are SqlDecimals too.

Burtsev, Dmitriy schrieb am 10.01.2017 um 15:33:
> Thank you for response.
>
> It looks like we are on different pages here. Let me start from the beginning.
>
> We are moving data between SQLite database and Microsoft SQL Server. At this 
> time we are using Excel files but we run into some Excel limitation.
> I am trying to change Excel files to SQLite database files.
>
> The test table has several NUMERIC type columns. We have NUMERIC(11,0), 
> NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) 
> column.
> It looks like the problem is not with NUMERIC type in general, but only when 
> precision is equal to scale.
>
> Exception calling "WriteToServer" with "1" argument(s): "The given value of 
> type SqlDecimal from the data source cannot be converted to type decimal of 
> the specified target column."
>
>
> -Original Message-
> From: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB
> Sent: Tuesday, January 10, 2017 2:08 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Need help with System.Data.SQLite
>
> System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and 
> thus the SQLite provider doesn't know how to handle it. Try using 
> System.Decimal as a more generic approach. If you need to be portable across 
> providers, you will be better off using classes from System.Data.Common 
> anyway.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> 

Re: [sqlite] Need help with System.Data.SQLite

[Hick Gunter]

What is the result if you exchange your currently executed statement to "SELECT 
1,0;" ? Or to "SELECT 1, cast(0 as real)"?

The effect of declaring a NUMERIC(5,5) field in SQLite is that it's affinity 
becomes NUMERIC. The value .0 thus becomes the integer 0. The other legal 
values (.1 to .9) are all converted to real (double) floating point 
numbers, each to the closest possible approximation (or, if the approximation 
is not "close enough", as text !!!).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Burtsev, Dmitriy
Gesendet: Mittwoch, 11. Jänner 2017 15:48
An: 'SQLite mailing list' 
Betreff: Re: [sqlite] Need help with System.Data.SQLite

I didn't assign any value directly.

This is what I did:

1.   On SQLite create source table.
CREATE TABLE FromNum (
id INT,
Num NUMERIC(5,5) NULL);
INSERT INTO FromNum (id, Num) VALUES (1, .0);

2.  On MS SQL Server 2014 (SP2) create empty destination table.

CREATE TABLE dbo.ToNum (
id INT NOT NULL ,
Num NUMERIC(5,5) NULL);

I can't copy my PowerShell script in this e-mail. Got error "The message's 
content type was not explicitly allowed". I will try pseudocode.

Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll'

Create System.Data.SQLite.SQLiteConnection

Connection CreateCommand()

CommandText = "SELECT * FROM FromNum"

System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader()

Create System.Data.SqlClient.SqlConnection
Create System.Data.SqlClient.SqlBulkCopy SqlConnection, 
[System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
SqlbulkCopy.DestinationTableName = "ToNum"
Sqlbulkcopy.EnableStreaming = true

SqlbulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here

This code is working with NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3) but not 
working with NUMERIC(5,5).

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of GB
Sent: Wednesday, January 11, 2017 2:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] Need help with System.Data.SQLite

SQLite does not really care about precision and scale. See 
https://sqlite.org/datatype3.html for more information.

The culprit lies in the use of SqlDecimal which cannot be used by 
System.Data.SQLite directly and has no implicit conversion to something 
understandable by System.Data.SQLite (for more information about SqlDecimal see 
https://msdn.microsoft.com/en-us/library/xaktx377(v=vs.110).aspx).

However, an explicit conversion exists, so an explicit cast to System.Decimal 
would work. Maybe you could try this at the Point where you assign the Value 
(more information about the explicit operator can be found here: 
https://msdn.microsoft.com/en-us/library/xhbhezf4.aspx).

Also have a close look at what is being fed into the other numeric columns. I 
doubt these are SqlDecimals too.

Burtsev, Dmitriy schrieb am 10.01.2017 um 15:33:
> Thank you for response.
>
> It looks like we are on different pages here. Let me start from the beginning.
>
> We are moving data between SQLite database and Microsoft SQL Server. At this 
> time we are using Excel files but we run into some Excel limitation.
> I am trying to change Excel files to SQLite database files.
>
> The test table has several NUMERIC type columns. We have NUMERIC(11,0), 
> NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) 
> column.
> It looks like the problem is not with NUMERIC type in general, but only when 
> precision is equal to scale.
>
> Exception calling "WriteToServer" with "1" argument(s): "The given value of 
> type SqlDecimal from the data source cannot be converted to type decimal of 
> the specified target column."
>
>
> -Original Message-
> From: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB
> Sent: Tuesday, January 10, 2017 2:08 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Need help with System.Data.SQLite
>
> System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and 
> thus the SQLite provider doesn't know how to handle it. Try using 
> System.Decimal as a more generic approach. If you need to be portable across 
> providers, you will be better off using classes from System.Data.Common 
> anyway.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> This message, and any of its attachments, is for the intended recipient(s) 
> only, and it may contain information that is privileged, confidential, and/or 
> proprietary and subject to important terms and conditions available at 
> http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the 
> intended recipient, please delete this message and immediately notify the 
> sender. No confidentiality, privilege, or property rights are waived 

Re: [sqlite] Problem with compiled dll on Windows

[Random Coder]

On Wed, Jan 11, 2017 at 2:11 AM, Rael Bauer  wrote:
> I am trying to compile the latest amalgamation (3160200) as a dll on Windows
> 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll compiles fine,
> however trying to use this dll in various tools (Delphi) results in the
> error "sqlite3_open not found".
>
> I tried compiling with:
> cl sqlite3.c -link -dll -out:sqlite3.dll  and
> cl sqlite3.c -DSQLITE_ENABLE_FTS4 -link -dll -out:sqlite3.dll


You're not including a .def file, so no functions are being exported
in your DLL.  You can verify this using depends (
http://www.dependencywalker.com/ ).  You can include the .def file
with something like this:

cl sqlite3.c -Ox -DSQLITE_ENABLE_FTS4 -link -dll -out:sqlite3.dll
-def:sqlite3.def

Here's a DLL compiled with this that has the exported functions, along
with the other artifacts of my build:

https://new-bucket-2a9cf983.s3.amazonaws.com/sqlite3_dll.zip
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with compiled dll on Windows

[James K. Lowden]

On Wed, 11 Jan 2017 12:11:36 +0200
Rael Bauer  wrote:

> I am trying to compile the latest amalgamation (3160200) as a dll on 
> Windows 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll 
> compiles fine, however trying to use this dll in various tools
> (Delphi) results in the error "sqlite3_open not found".

Your question regards tooling, not SQLite.  

Did the compilation produce an import library, and did you link to that
import library in building your Delphi app?  The import library
describes the entry points in the the DLL.  Without it, the linker has
no way to resolve references supplied by the DLL.  

If memory serves, there's a windows tool dumpbin.exe that shows various
aspects of binaries.  It will confirm that sqlite3_open is exported by
the DLL.  It's then up to you to show Delphi where to find it.  

HTH.  

--jkl

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

Re: [sqlite] Need help with System.Data.SQLite

[Burtsev , Dmitriy]

I didn't assign any value directly.

This is what I did:

1.   On SQLite create source table.
CREATE TABLE FromNum (
id INT,
Num NUMERIC(5,5) NULL);
INSERT INTO FromNum (id, Num) VALUES (1, .0);

2.  On MS SQL Server 2014 (SP2) create empty destination table.

CREATE TABLE dbo.ToNum (
id INT NOT NULL ,
Num NUMERIC(5,5) NULL);

I can't copy my PowerShell script in this e-mail. Got error "The message's 
content type was not explicitly allowed". I will try pseudocode.

Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll'

Create System.Data.SQLite.SQLiteConnection

Connection CreateCommand()

CommandText = "SELECT * FROM FromNum"

System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader()

Create System.Data.SqlClient.SqlConnection
Create System.Data.SqlClient.SqlBulkCopy SqlConnection, 
[System.Data.SqlClient.SqlBulkCopyOptions]::TableLock
SqlbulkCopy.DestinationTableName = "ToNum"
Sqlbulkcopy.EnableStreaming = true

SqlbulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here

This code is working with NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3) but not 
working with NUMERIC(5,5).

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of GB
Sent: Wednesday, January 11, 2017 2:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] Need help with System.Data.SQLite

SQLite does not really care about precision and scale. See 
https://sqlite.org/datatype3.html for more information.

The culprit lies in the use of SqlDecimal which cannot be used by 
System.Data.SQLite directly and has no implicit conversion to something 
understandable by System.Data.SQLite (for more information about SqlDecimal see 
https://msdn.microsoft.com/en-us/library/xaktx377(v=vs.110).aspx).

However, an explicit conversion exists, so an explicit cast to System.Decimal 
would work. Maybe you could try this at the Point where you assign the Value 
(more information about the explicit operator can be found here: 
https://msdn.microsoft.com/en-us/library/xhbhezf4.aspx).

Also have a close look at what is being fed into the other numeric columns. I 
doubt these are SqlDecimals too.

Burtsev, Dmitriy schrieb am 10.01.2017 um 15:33:
> Thank you for response.
>
> It looks like we are on different pages here. Let me start from the beginning.
>
> We are moving data between SQLite database and Microsoft SQL Server. At this 
> time we are using Excel files but we run into some Excel limitation.
> I am trying to change Excel files to SQLite database files.
>
> The test table has several NUMERIC type columns. We have NUMERIC(11,0), 
> NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) 
> column.
> It looks like the problem is not with NUMERIC type in general, but only when 
> precision is equal to scale.
>
> Exception calling "WriteToServer" with "1" argument(s): "The given value of 
> type SqlDecimal from the data source cannot be converted to type decimal of 
> the specified target column."
>   
>   
> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB
> Sent: Tuesday, January 10, 2017 2:08 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Need help with System.Data.SQLite
>
> System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and 
> thus the SQLite provider doesn't know how to handle it. Try using 
> System.Decimal as a more generic approach. If you need to be portable across 
> providers, you will be better off using classes from System.Data.Common 
> anyway.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> This message, and any of its attachments, is for the intended recipient(s) 
> only, and it may contain information that is privileged, confidential, and/or 
> proprietary and subject to important terms and conditions available at 
> http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the 
> intended recipient, please delete this message and immediately notify the 
> sender. No confidentiality, privilege, or property rights are waived or lost 
> by any errors in transmission.
> ___
> 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] Foreign key error...

[Ken Wagner]

Keith,

Good point. Did not know this exists.

Ken


On 01/10/2017 09:48 PM, Simon Slavin wrote:

On 11 Jan 2017, at 1:02am, Keith Medcalf  wrote:


You are correct, however, if there were a unique constraint placed on 
tracks.name, then a given track could only appear once (in the first case), or 
in multiple places (in the second case).

_The Power of Love_ was recorded by Frankie Goes to Hollywood, Jennifer Rush, 
and Huey Lewis and The News.  Not only are these different recordings, they’re 
different songs.

Nor can you place a UNIQUE requirement on the combination of (title,authors).  
There are three different /studio/ edits of Bowie’s _Heroes_, and two of them 
are different enough that someone might like one and not the other.  Not to 
mention numerous different live versions which appear on convert albums and 
DVDs.

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] About ticket c92ecff2ec5f1784 LEFT JOIN problem

[Ken Wagner]

Domingo,

Thanks for the email, but I don't think I am your inteded recipient.

Ken


On 01/10/2017 12:11 PM, Domingo Alvarez Duarte wrote:

Hello Richard !

Now that you are dealing with this ticket 
http://www.sqlite.org/src/info/c92ecff2ec5f1784 could be a good moment 
to acknowledge the problem reported before (see bellow), I'm not sure 
why you have answered this way without acknowledge any further action 
on it. I was expecting a bug ticket for it or at minimum a 
documentation of this weird behavior of the sqlite3 planner so other 
people could be aware of it.


Cheers !

===

On 1/5/17, Domingo Alvarez Duarte wrote:


Hello !

Today I found this unexpected behavior when using sqlite3 trunk:

When using views with joins sqlite3 is choosing expected plans except
for "LEFT JOIN", bellow is the snippet that shows this unexpected 
behavior.


===

create table if not exists a(id integer primary key, val text);
create table if not exists b(id integer primary key, a_id integer not
null, val text);
create view if not exists b_view as select b.*, a.* from b left join a
on b.a_id=a.id;
create table if not exists c(id integer primary key, b_id integer not
null, val text);

select 'bad unexpected plan';
explain query plan select c.*, b_view.* from c left join b_view on
c.b_id=b_view.id;


Can you rewrite your query as:

  SELECT *
   FROM c LEFT JOIN b ON c.b_id=b.id
LEFT JOIN a ON b.id=a.id;
-- D. Richard Hipp

===

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


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

Re: [sqlite] Problem with compiled dll on Windows

[Dominique Devienne]

On Wed, Jan 11, 2017 at 11:11 AM, Rael Bauer  wrote:

> I am trying to compile the latest amalgamation (3160200) as a dll on
> Windows 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll
> compiles fine, however trying to use this dll in various tools (Delphi)
> results in the error "sqlite3_open not found".
>
> I tried compiling with:
> cl sqlite3.c -link -dll -out:sqlite3.dll  and
> cl sqlite3.c -DSQLITE_ENABLE_FTS4 -link -dll -out:sqlite3.dll
>
> But experienced the same problem. Any ideas what could be wrong?
>

Might be a cdecl vs stdcall issue. See for example the SO post below. --DD

http://stackoverflow.com/questions/13558530/how-do-i-export-function-from-c-dll-and-use-in-delphi
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with compiled dll on Windows

[Rael Bauer]

Hi,

I am trying to compile the latest amalgamation (3160200) as a dll on 
Windows 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll 
compiles fine, however trying to use this dll in various tools (Delphi) 
results in the error "sqlite3_open not found".


I tried compiling with:
cl sqlite3.c -link -dll -out:sqlite3.dll  and
cl sqlite3.c -DSQLITE_ENABLE_FTS4 -link -dll -out:sqlite3.dll

But experienced the same problem. Any ideas what could be wrong?

I have uploaded the dll's to:
www.bauerapps.com/files/sqlite3_1.dll  and
www.bauerapps.com/files/sqlite3_2.dll

Here is the command line log:
C:\Program Files (x86)\Microsoft Visual Studio 14.0\VC>cd 
C:\Downloads\Database\SQLite\sqlite-amalgamation-3160200



C:\Downloads\Database\SQLite\sqlite-amalgamation-3160200>cl sqlite3.c 
-link -dll -out:sqlite3.dll


Microsoft (R) C/C++ Optimizing Compiler Version 19.00.23918 for x86

Copyright (C) Microsoft Corporation. All rights reserved.


sqlite3.c

Microsoft (R) Incremental Linker Version 14.00.23918.0

Copyright (C) Microsoft Corporation. All rights reserved.


/out:sqlite3.exe

-dll

-out:sqlite3.dll

sqlite3.obj


C:\Downloads\Database\SQLite\sqlite-amalgamation-3160200>cl sqlite3.c 
-DSQLITE_ENABLE_FTS4 -link -dll -out:sqlite3.dll


Microsoft (R) C/C++ Optimizing Compiler Version 19.00.23918 for x86

Copyright (C) Microsoft Corporation. All rights reserved.


sqlite3.c

Microsoft (R) Incremental Linker Version 14.00.23918.0

Copyright (C) Microsoft Corporation. All rights reserved.


/out:sqlite3.exe

-dll

-out:sqlite3.dll

sqlite3.obj


C:\Downloads\Database\SQLite\sqlite-amalgamation-3160200>

Thanks




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