Re: [sqlite] Mailinglist question

[Wolfgang Enzinger]

Am Thu, 10 Aug 2017 01:37:14 +0200 schrieb Wolfgang Enzinger:

> I'm connected to this mailing list as if it was a newsgroup using
> nntp://news.gmane.org:563/gmane.comp.db.sqlite.general.

Bummer ... just a few hours after I posted this, that service seems to have
stopped pulling any new posts - before that point in time I had used it for
years without any issue.

This is a test now if my message comes thru.

On their website I read that they're undergoing some reconstruction
process, hopefully the NNTP service will resurrect, I really like it.

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

[sqlite] Possible SQLite bug using an uncollated index with a collated query

[Jens Alfke]

I believe I may have found a bug in SQLite 3.18. I've got a query that returns 
a correct result set when there are no indexes in the database, but returns an 
incorrect result set if recompiled after an index has been added. The incorrect 
result set has no collation applied, so the problem seems to be that when 
SQLite uses the index to optimize the query, it forgets to apply collation when 
grouping and ordering the results, so the results are in uncollated order as 
they appeared in the index.

Here's a slightly simplified* version of the query. This just returns a sorted 
list of all the unique artist names (the database is generated from an iTunes 
library, with each row containing a JSON object representing a track.) The 
"LCUnicode_CD_" custom collation compares UTF-8-encoded Unicode strings 
ignoring case and diacritics; it's been tested enough that I trust it.

SELECT json_extract(body, '$.Artist') 
FROM kv_default 
WHERE json_extract body, '$.Artist') IS NOT NULL AND json_extract body, 
'$.Compilation') IS NULL 
GROUP BY json_extract(body, '$.Artist') COLLATE LCUnicode_CD_ 
ORDER BY json_extract(body, '$.Artist') COLLATE LCUnicode_CD_;

With no index in the database, this query returns the correct results. Artists 
are sorted ignoring case and diacritical marks, and artist names that have 
inconsistent case or diacritics on different tracks (like "The B-52s" vs "The 
b-52s", or "Björk" vs "Bjork") only appear once.

Now I add an index:

CREATE INDEX byArtist 
ON kv_default (json_extract(body, '$.Compilation'), json_extract(body, 
'$.Artist'))

After creating the index, recompiling and running the query now produces 
incorrect results: the collation is ignored, so the sort order is 
case-sensitive, accented letters appear after Z, and duplicate artist names 
with different capitalization/accents appear.

Before the index is added, EXPLAIN QUERY PLAN gives:
0|0|0| SCAN TABLE kv_default
0|0|0| USE TEMP B-TREE FOR GROUP BY

After the index is added, it gives:
0|0|0| SEARCH TABLE kv_default USING INDEX byArtist (=?)

It's forgotten that it needs to sort/group the rows coming from the index, 
because the index isn't collated. :(

I haven't yet tried to create a minimal reproduction, but I'm guessing I could 
reproduce this without using JSON functions or custom collations.

—Jens

* I've omitted some irrelevant result columns, and substituted json_extract for 
the actual custom function I use which has similar behavior (extracting a value 
out of an encoded blob.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] tclsqlite3.c and libtclsqlite3.so not built

[Roderick]

Dear Sirs,

I am trying to build sqlite 3.20 in FreeBSD 11.1.

configure, make and make install does not build the tcl interface.

I configure with --enable-tcl --with-tcl=/usr/opt/tcl86/lib.

At the end, I get only pkgIndex.tcl, not libtclsqlite3.so
in /usr/opt/tcl86/lib/tcl8.6/sqlite3.

Neither tclsqlite3.c nor libtclsqlite3.so are built.

This is not the behauviour described in the Makefile:




# The following variable is "1" if the configure script was able to locate
# the tclConfig.sh file.  It is an empty string otherwise.  When this
# variable is "1", the TCL extension library (libtclsqlite3.so) is built
# and installed.
#
HAVE_TCL = 1

# This is the command to use for tclsh - normally just "tclsh", but we may
# know the specific version we want to use
#
TCLSH_CMD = tclsh8.6

# Where do we want to install the tcl plugin
#
TCLLIBDIR = /usr/opt/tcl86/lib/tcl8.6/sqlite3

# The suffix used on shared libraries.  Ex:  ".dll", ".so", ".dylib"
#
SHLIB_SUFFIX = .so
<<

I do get file libtclsqlite3.la in the build directory (I do not know
for what is it), but not tclsqlite3.c.

I did the following to get the missed files:




make tclsqlite3.c
gcc5 -o libtclsqlite3.so -fPIC -shared tclsqlite3.c \
 -lpthread -ltcl8.6 -I/usr/opt/tcl86/include -L/usr/opt/tcl86/lib
<<

I thank you for any hint (to my Email address, I am not in the list)
and eventually bug fix.

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

Re: [sqlite] Custom API Server and custom database

[J Decker]

On Wed, Aug 16, 2017 at 1:11 PM, Henry Udoye  wrote:

> --
> Henry Udoye.
> 4165 Whiteplains Road.
> P. O. Box 1290.
> Bronx. N. Y. 10466
> Phone : 914-316-1854.
> Fax : 206-350-3803
> hudoye1...@gmail.com
>
>
>
>
>
> Can you supply or provide the following:
>
>
>
> 1.   Create custom APIs server with multiple end points and multiple
> clients
>
no

>
> 2.   Build a Custom database
>
create table...  https://sqlite.org/lang_createtable.html

>
> 3.  Integrate APIs
>
> No

>
>
> Build a database –including enabling or integration and configuration of
> APIs
> Search or extract the search engines content, data, information, etc.
> through or via search engines queries.
>
 https://sqlite.org/lang_select.html

Store the results or findings of the search in the database.
>
 https://sqlite.org/lang_insert.html

> Query or extract or retrieve (including query conditions, sub-query,
> criteria, requirements, parameters) the database content or data for:
> Affiliate programs
> Information, news, current affairs or events.
> Reseller programs
>
see select


> Store the results or findings of the search in the database for affiliate
> programs
> information, news, current affairs or events, reseller programs in the
> database.
>
see insert


> Query or extract or retrieve (including query conditions, sub-query,
> criteria, requirements, parameters) the affiliate programs in the database
> content or data for:
> Affiliate products and /or services
> Query or extract or retrieve (including query conditions, sub-query,
> criteria, requirements, parameters) the reseller programs in the database
> content or data for:
> Reseller products and/or services.
>
see select


> Integrate, configure, connect tasks APIs.
> Project APIs
> Business Partners APIs include the affiliate programs. For examples Click
> Bank, Warrior Plus, JVzoo etc.
> Business Partners APIs include the Reseller programs. For examples
> Microsoft, Hostgator, etc.
> Public APIs include the social media, Twitter, WordPress etc.
> Catalog of APIs:
>

This is the job of an application; being described, that you will have to
write yourself and is out of the realm of sqlite; although you can use
sqlite to store information for such things.  (same as any other database)



> ___
> 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] Error trying to do inner joins

[David Raymond]

Correct. For normal* cases inner joins are where you want only things that 
match in table A and table B, then you expand that out with multiple inner 
joins to get to 3 or more tables. Order doesn't matter in an inner join, so A 
INNER JOIN B will be equivalent to B INNER JOIN A.


Outer joins are where you want everything that matches in A and B, but also 
want everything from table A that didn't match to B. Outer joins are order 
sensitive, so A LEFT OUTER JOIN B is different than B LEFT OUTER JOIN A


* "Normal" being equi-joins of the type ...ON A.x = B.x (and A.y = B.y...)
You can throw inequalities into the ON clause there, or just about any 
statement that can be cast to a boolean in the end, but 99% of the time you're 
gonna see just the = operator.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Lars Frederiksen
Sent: Thursday, August 17, 2017 3:53 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Error trying to do inner joins

Thank you,

This SQL works fine. 
Is the inner join normally used for connecting 3 tables where the "middle one" 
is a link table?

/Lars



-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af David Raymond
Sendt: 17. august 2017 21:16
Til: SQLite mailing list
Emne: Re: [sqlite] Error trying to do inner joins

There's an issue with the join statement in the first join you're joining 
Saetning and Verb_Saetn, but the ON clause uses fields in Verb_Saetn and 
Verbum, not Saetning. And then you join that back to Saetning, so you've joined 
Saetning twice and Verbum 0 times. Since the Saetning table shows up twice in 
your join that's why the fields are ambiguous.

Try...

...FROM Saetning INNER JOIN Verb_Saetn ON Saetning.SAETNING_ID = 
Verb_Saetn.SAETNING_ID INNER JOIN Verbum ON Verb_Saetn.VERBUM_ID = 
Verbum.VERBUM_ID

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Lars Frederiksen
Sent: Thursday, August 17, 2017 2:54 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Error trying to do inner joins

Hello,

 

I have a small db with 3 tables:

 

 

CREATE TABLE IF NOT EXISTS "Verb_Saetn"(

VS_ID INT PRIMARY KEY NOT NULL,

VERBUM_ID INT NOT NULL,

SAETNING_ID INT NOT NULL,

FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID),

FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID)

);

 

CREATE TABLE IF NOT EXISTS "Verbum"(

   VERBUM_ID  INT PRIMARY KEY NOT NULL,

   NUTID  VARCHAR(40) NULL,

   DATID_IMPF VARCHAR(40) NULL,

   DATID_PERF VARCHAR(40) NULL,

   FREMTID_IMPF   VARCHAR(40) NULL,

   FREMTID_PERF   VARCHAR(40) NULL,

   GULINTNOT NULL,

   TYPE   VARCHAR(20) NOT NULL,

   NOTE   TEXTNULL

);

 

CREATE TABLE IF NOT EXISTS "Saetning"(

SAETNING_ID INT PRIMARY KEY NOT NULL,

SENTENCE_EN VARCHAR(200) NOT NULL,

SENTENCE_EL VARCHAR(200) NOT NULL

);

 

 

The Verb_Saetn table is a link table between Verbum and Saetning (sentence in 
danish).

I try this SQL text:

 

 

SELECT

SENTENCE_EN,

SENTENCE_EL

FROM Saetning

INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID

INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID;

 

But get this error: ambiguous column name: SENTENCE_EN

 

- reason of this error?

- is inner joins the correct way to "connect" two tebles via a "link-table"?

 

Best Regards

Lars

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

___
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] Error trying to do inner joins

[Keith Medcalf]

Yes.  Or you could toss the new fangled JOIN syntax (which does nothing if the 
join is not an outer join) which only adds silly complication and do something 
like:

SELECT sentece_en,
   sentence_el
  FROM Saetning, Verb_Saetn, Verbum
 WHERE Saetning.Saeting_ID == Vert_Saetn.Saetning_ID
   AND Verb_Saetn.VERBUM_ID == Verbum.VERBUM_ID;

Much more obvious what you are doing.  Symantically it is exactly the same as 
David's query in the new fangled syntax since that query is merely written into 
the form above (the keyword JOIN or INNER JOIN is merely replaced by a ",", and 
the conditions in ON  are merely pushed into the where clause and 
joined by AND).

Since the contents of the ON clause are pushed down as where conditions, there 
is absolutely no requirement for the tables used in the ON clauses to have been 
seen prior to there use.  They only need to exist when the optimizer attempts 
to generate a query plan (at least this is the case for all sane SQL 
processors, of which SQLite is one).

You need to have indexes on your "foreign" key fields, and you probably want an 
index on Vert_Saetn containing the link keys Saetning_ID and VERBUM_ID, 
probably two indexes with the fields in both orders.  Of course, that depends 
if you database will ever hold more than 10 records per table (if it will, then 
you will need all the indexes).

---
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 Lars Frederiksen
>Sent: Thursday, 17 August, 2017 13:53
>To: 'SQLite mailing list'
>Subject: Re: [sqlite] Error trying to do inner joins
>
>Thank you,
>
>This SQL works fine.
>Is the inner join normally used for connecting 3 tables where the
>"middle one" is a link table?
>
>/Lars
>
>
>
>-Oprindelig meddelelse-
>Fra: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] På vegne af David Raymond
>Sendt: 17. august 2017 21:16
>Til: SQLite mailing list
>Emne: Re: [sqlite] Error trying to do inner joins
>
>There's an issue with the join statement in the first join you're
>joining Saetning and Verb_Saetn, but the ON clause uses fields in
>Verb_Saetn and Verbum, not Saetning. And then you join that back to
>Saetning, so you've joined Saetning twice and Verbum 0 times. Since
>the Saetning table shows up twice in your join that's why the fields
>are ambiguous.
>
>Try...
>
>...FROM Saetning INNER JOIN Verb_Saetn ON Saetning.SAETNING_ID =
>Verb_Saetn.SAETNING_ID INNER JOIN Verbum ON Verb_Saetn.VERBUM_ID =
>Verbum.VERBUM_ID
>
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Lars Frederiksen
>Sent: Thursday, August 17, 2017 2:54 PM
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Error trying to do inner joins
>
>Hello,
>
>
>
>I have a small db with 3 tables:
>
>
>
>
>
>CREATE TABLE IF NOT EXISTS "Verb_Saetn"(
>
>VS_ID INT PRIMARY KEY NOT NULL,
>
>VERBUM_ID INT NOT NULL,
>
>SAETNING_ID INT NOT NULL,
>
>FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID),
>
>FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID)
>
>);
>
>
>
>CREATE TABLE IF NOT EXISTS "Verbum"(
>
>   VERBUM_ID  INT PRIMARY KEY NOT NULL,
>
>   NUTID  VARCHAR(40) NULL,
>
>   DATID_IMPF VARCHAR(40) NULL,
>
>   DATID_PERF VARCHAR(40) NULL,
>
>   FREMTID_IMPF   VARCHAR(40) NULL,
>
>   FREMTID_PERF   VARCHAR(40) NULL,
>
>   GULINTNOT NULL,
>
>   TYPE   VARCHAR(20) NOT NULL,
>
>   NOTE   TEXTNULL
>
>);
>
>
>
>CREATE TABLE IF NOT EXISTS "Saetning"(
>
>SAETNING_ID INT PRIMARY KEY NOT NULL,
>
>SENTENCE_EN VARCHAR(200) NOT NULL,
>
>SENTENCE_EL VARCHAR(200) NOT NULL
>
>);
>
>
>
>
>
>The Verb_Saetn table is a link table between Verbum and Saetning
>(sentence in danish).
>
>I try this SQL text:
>
>
>
>
>
>SELECT
>
>SENTENCE_EN,
>
>SENTENCE_EL
>
>FROM Saetning
>
>INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID
>
>INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID;
>
>
>
>But get this error: ambiguous column name: SENTENCE_EN
>
>
>
>- reason of this error?
>
>- is inner joins the correct way to "connect" two tebles via a "link-
>table"?
>
>
>
>Best Regards
>
>Lars
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list

Re: [sqlite] Error trying to do inner joins

[Lars Frederiksen]

Thank you,

This SQL works fine. 
Is the inner join normally used for connecting 3 tables where the "middle one" 
is a link table?

/Lars



-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af David Raymond
Sendt: 17. august 2017 21:16
Til: SQLite mailing list
Emne: Re: [sqlite] Error trying to do inner joins

There's an issue with the join statement in the first join you're joining 
Saetning and Verb_Saetn, but the ON clause uses fields in Verb_Saetn and 
Verbum, not Saetning. And then you join that back to Saetning, so you've joined 
Saetning twice and Verbum 0 times. Since the Saetning table shows up twice in 
your join that's why the fields are ambiguous.

Try...

...FROM Saetning INNER JOIN Verb_Saetn ON Saetning.SAETNING_ID = 
Verb_Saetn.SAETNING_ID INNER JOIN Verbum ON Verb_Saetn.VERBUM_ID = 
Verbum.VERBUM_ID

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Lars Frederiksen
Sent: Thursday, August 17, 2017 2:54 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Error trying to do inner joins

Hello,

 

I have a small db with 3 tables:

 

 

CREATE TABLE IF NOT EXISTS "Verb_Saetn"(

VS_ID INT PRIMARY KEY NOT NULL,

VERBUM_ID INT NOT NULL,

SAETNING_ID INT NOT NULL,

FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID),

FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID)

);

 

CREATE TABLE IF NOT EXISTS "Verbum"(

   VERBUM_ID  INT PRIMARY KEY NOT NULL,

   NUTID  VARCHAR(40) NULL,

   DATID_IMPF VARCHAR(40) NULL,

   DATID_PERF VARCHAR(40) NULL,

   FREMTID_IMPF   VARCHAR(40) NULL,

   FREMTID_PERF   VARCHAR(40) NULL,

   GULINTNOT NULL,

   TYPE   VARCHAR(20) NOT NULL,

   NOTE   TEXTNULL

);

 

CREATE TABLE IF NOT EXISTS "Saetning"(

SAETNING_ID INT PRIMARY KEY NOT NULL,

SENTENCE_EN VARCHAR(200) NOT NULL,

SENTENCE_EL VARCHAR(200) NOT NULL

);

 

 

The Verb_Saetn table is a link table between Verbum and Saetning (sentence in 
danish).

I try this SQL text:

 

 

SELECT

SENTENCE_EN,

SENTENCE_EL

FROM Saetning

INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID

INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID;

 

But get this error: ambiguous column name: SENTENCE_EN

 

- reason of this error?

- is inner joins the correct way to "connect" two tebles via a "link-table"?

 

Best Regards

Lars

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

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

Re: [sqlite] Error trying to do inner joins

[David Raymond]

There's an issue with the join statement in the first join you're joining 
Saetning and Verb_Saetn, but the ON clause uses fields in Verb_Saetn and 
Verbum, not Saetning. And then you join that back to Saetning, so you've joined 
Saetning twice and Verbum 0 times. Since the Saetning table shows up twice in 
your join that's why the fields are ambiguous.

Try...

...FROM Saetning INNER JOIN Verb_Saetn ON Saetning.SAETNING_ID = 
Verb_Saetn.SAETNING_ID
INNER JOIN Verbum ON Verb_Saetn.VERBUM_ID = Verbum.VERBUM_ID

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Lars Frederiksen
Sent: Thursday, August 17, 2017 2:54 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Error trying to do inner joins

Hello,

 

I have a small db with 3 tables:

 

 

CREATE TABLE IF NOT EXISTS "Verb_Saetn"(

VS_ID INT PRIMARY KEY NOT NULL,

VERBUM_ID INT NOT NULL,

SAETNING_ID INT NOT NULL,

FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID),

FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID)

);

 

CREATE TABLE IF NOT EXISTS "Verbum"(

   VERBUM_ID  INT PRIMARY KEY NOT NULL,

   NUTID  VARCHAR(40) NULL,

   DATID_IMPF VARCHAR(40) NULL,

   DATID_PERF VARCHAR(40) NULL,

   FREMTID_IMPF   VARCHAR(40) NULL,

   FREMTID_PERF   VARCHAR(40) NULL,

   GULINTNOT NULL,

   TYPE   VARCHAR(20) NOT NULL,

   NOTE   TEXTNULL

);

 

CREATE TABLE IF NOT EXISTS "Saetning"(

SAETNING_ID INT PRIMARY KEY NOT NULL,

SENTENCE_EN VARCHAR(200) NOT NULL,

SENTENCE_EL VARCHAR(200) NOT NULL

);

 

 

The Verb_Saetn table is a link table between Verbum and Saetning (sentence
in danish).

I try this SQL text:

 

 

SELECT

SENTENCE_EN,

SENTENCE_EL

FROM Saetning

INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID

INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID;

 

But get this error: ambiguous column name: SENTENCE_EN

 

- reason of this error?

- is inner joins the correct way to "connect" two tebles via a "link-table"?

 

Best Regards

Lars

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

Re: [sqlite] Error trying to do inner joins

[R Smith]

On 2017/08/17 8:54 PM, Lars Frederiksen wrote:

Hello,

  


I have a small db with 3 tables:

  

  


CREATE TABLE IF NOT EXISTS "Verb_Saetn"(

VS_ID INT PRIMARY KEY NOT NULL,

VERBUM_ID INT NOT NULL,

SAETNING_ID INT NOT NULL,

FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID),

FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID)

);

  


CREATE TABLE IF NOT EXISTS "Verbum"(

VERBUM_ID  INT PRIMARY KEY NOT NULL,

NUTID  VARCHAR(40) NULL,

DATID_IMPF VARCHAR(40) NULL,

DATID_PERF VARCHAR(40) NULL,

FREMTID_IMPF   VARCHAR(40) NULL,

FREMTID_PERF   VARCHAR(40) NULL,

GULINTNOT NULL,

TYPE   VARCHAR(20) NOT NULL,

NOTE   TEXTNULL

);

  


CREATE TABLE IF NOT EXISTS "Saetning"(

SAETNING_ID INT PRIMARY KEY NOT NULL,

SENTENCE_EN VARCHAR(200) NOT NULL,

SENTENCE_EL VARCHAR(200) NOT NULL

);

  

  


The Verb_Saetn table is a link table between Verbum and Saetning (sentence
in danish).

I try this SQL text:

  

  


SELECT

SENTENCE_EN,

SENTENCE_EL

FROM Saetning

INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID

INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID;

  


But get this error: ambiguous column name: SENTENCE_EN


It means the Field SENTENCE_EN appears in more than one table and you 
did not qualify it, os the Query planner doesn't know which one you mean.

Try:
SELECT
  Saetning.SENTENCE_EN...





  


- reason of this error?

- is inner joins the correct way to "connect" two tebles via a "link-table"?


Seems weird, it is better to put the filter references to a joined table 
AFTER the table is actually joined, but more importantly, where is table 
"Verbum" table being joined? You refer to it, but it's not in the join list.


Did you perhaps mean to do this?:

SELECT

Saetning.SENTENCE_EN,

Saetning.SENTENCE_EL

FROM Saetning

INNER JOIN Verb_Saetn ON Verb_Saetn.Saetning_ID = Saetning.Saetning_ID

INNER JOIN Verbum ON Verbum.Verbum_ID = Verb_Saetn.Verbum_ID;


Also - SQLite is very forgiving in terms of Case-sensitivity for Field, table 
and general object identifiers, but other engines will not know that Verbum_ID 
and VERBUM_ID is the same thing, and error out. Check your consistency.

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

[sqlite] Error trying to do inner joins

[Lars Frederiksen]

Hello,

 

I have a small db with 3 tables:

 

 

CREATE TABLE IF NOT EXISTS "Verb_Saetn"(

VS_ID INT PRIMARY KEY NOT NULL,

VERBUM_ID INT NOT NULL,

SAETNING_ID INT NOT NULL,

FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID),

FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID)

);

 

CREATE TABLE IF NOT EXISTS "Verbum"(

   VERBUM_ID  INT PRIMARY KEY NOT NULL,

   NUTID  VARCHAR(40) NULL,

   DATID_IMPF VARCHAR(40) NULL,

   DATID_PERF VARCHAR(40) NULL,

   FREMTID_IMPF   VARCHAR(40) NULL,

   FREMTID_PERF   VARCHAR(40) NULL,

   GULINTNOT NULL,

   TYPE   VARCHAR(20) NOT NULL,

   NOTE   TEXTNULL

);

 

CREATE TABLE IF NOT EXISTS "Saetning"(

SAETNING_ID INT PRIMARY KEY NOT NULL,

SENTENCE_EN VARCHAR(200) NOT NULL,

SENTENCE_EL VARCHAR(200) NOT NULL

);

 

 

The Verb_Saetn table is a link table between Verbum and Saetning (sentence
in danish).

I try this SQL text:

 

 

SELECT

SENTENCE_EN,

SENTENCE_EL

FROM Saetning

INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID

INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID;

 

But get this error: ambiguous column name: SENTENCE_EN

 

- reason of this error?

- is inner joins the correct way to "connect" two tebles via a "link-table"?

 

Best Regards

Lars

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

Re: [sqlite] Explain results help...

[R Smith]

On 2017/08/17 2:40 PM, jose isaias cabrera wrote:


Jens Alfke wrote...

On Aug 16, 2017, at 8:36 PM, jose isaias cabrera 
 wrote:


The following query is taking a bit too long for my taste.  I did a 
quick query with explain and I got this…


The output of EXPLAIN QUERY PLAN is a lot easier to understand; have 
you tried that? Most
importantly it shows which indexes / tables are being searched (fast) 
or scanned (slow),

and if a covering index is used.


sqlite> ATTACH 
'L:\Data\OpenJobsTool\Selva.Shared.DB.UK.AllOpenProjs.db' AS client;

sqlite>
sqlite> explain query plan
  ...> SELECT ls.id FROM LSOpenProjects ls JOIN client.LSOpenProjects 
cl ON

  ...> (
  ...>   cl.login != 'lsuser' AND
  ...>   cl.id = ls.id AND
  ...>   cl.XtraB != ls.XtraB
  ...> ) LIMIT 100;
0|0|1|SCAN TABLE LSOpenProjects AS cl USING COVERING INDEX 
Proj_id_login_XtraB
0|1|0|SEARCH TABLE LSOpenProjects AS ls USING INTEGER PRIMARY KEY 
(rowid=?)

sqlite>

from what this means, I guess what I need to do is to re-arrange the 
table...


Perhaps not. That simply states that it's doing one scan and one lookup, 
smartly picking the table with more restrictive filtering for the scan. 
I can't see a query plan that would be any faster unless we don't have 
all the info.


I don't know how many ID's in the client table will match that of the 
main table, but I'm thinking they might both be primary keys so it will 
be 1-to-1 and it will require one table-scan and one lookup no matter 
which way you pose the question or order the table.


If there are multiple possible same ID's in the client table, things 
change, and since you are only interested in an ID with matching 
criteria in the attached DB, you could probably get faster results when 
using an EXISTS check on the client DB in stead of the join, but if ID 
is primary on the client too, then that's not viable. Also, if they are 
both primary, a covering index that starts on a primary key is moot in 
terms of lookup speed, but it might still offer improved read speed but 
at a severe INSERT speed penalty.


How many records are in these two tables? What is "too long" for your 
taste? It's hard to guess at solutions (or even possible problems) 
without some specifics.



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

Re: [sqlite] Explain results help...

[Simon Slavin]

> On 17 Aug 2017, at 1:40pm, jose isaias cabrera  wrote:
> 
> sqlite> explain query plan
>  ...> SELECT ls.id FROM LSOpenProjects ls JOIN client.LSOpenProjects cl ON
>  ...> (
>  ...>   cl.login != 'lsuser' AND
>  ...>   cl.id = ls.id AND
>  ...>   cl.XtraB != ls.XtraB
>  ...> ) LIMIT 100;
> 0|0|1|SCAN TABLE LSOpenProjects AS cl USING COVERING INDEX Proj_id_login_XtraB
> 0|1|0|SEARCH TABLE LSOpenProjects AS ls USING INTEGER PRIMARY KEY (rowid=?)
> sqlite>
> 
> from what this means, I guess what I need to do is to re-arrange the table...

Nope.  Just create an appropriate index.  Try doing this

CREATE INDEX test1 ON client.LSOpenProjects(login,XtraB);
ANALYZE

Then try the EXPLAIN QUERY PLAN again.  Though your query is unusual and it’s 
possible that SQLite has already found a good way to execute your command.

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

Re: [sqlite] Compiler warning "gethostuuid() is disabled" building SQLite for iOS

[Bob Friesenhahn]

On Wed, 16 Aug 2017, Richard Hipp wrote:


On 8/16/17, Simon Slavin  wrote:


I worked at a university and we used network home directories.



Why use the local disk when you can instead use a network filesystem
that is less reliable and about 100x slower?



It is not necessarily the case that a network filesystem is less 
reliable or 100x slower.  The server may have much better storage 
reliability (RAID) and performance characteristics (faster storage 
devices and more much more RAM) and accessing server-based storage may 
actually have much better latency than local disk on a typical 
computer.  Files on the server are more readily backed up than files 
distributed across many hosts.


I did use NFS for accessing user files while I had an Apple OS X 
system in active use here.  The OS X NFS seemed to be slower than 
normal compared to the NFS provided by other operating systems.


I am still using NFS mounted home directories across all systems here 
(since 1993).  No significant problems have been encountered during 
that time.


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] Explain results help...

[jose isaias cabrera]

Jens Alfke wrote...

On Aug 16, 2017, at 8:36 PM, jose isaias cabrera  
wrote:


The following query is taking a bit too long for my taste.  I did a quick 
query with explain and I got this…


The output of EXPLAIN QUERY PLAN is a lot easier to understand; have you 
tried that? Most
importantly it shows which indexes / tables are being searched (fast) or 
scanned (slow),

and if a covering index is used.


sqlite> ATTACH 'L:\Data\OpenJobsTool\Selva.Shared.DB.UK.AllOpenProjs.db' AS 
client;

sqlite>
sqlite> explain query plan
  ...> SELECT ls.id FROM LSOpenProjects ls JOIN client.LSOpenProjects cl ON
  ...> (
  ...>   cl.login != 'lsuser' AND
  ...>   cl.id = ls.id AND
  ...>   cl.XtraB != ls.XtraB
  ...> ) LIMIT 100;
0|0|1|SCAN TABLE LSOpenProjects AS cl USING COVERING INDEX 
Proj_id_login_XtraB

0|1|0|SEARCH TABLE LSOpenProjects AS ls USING INTEGER PRIMARY KEY (rowid=?)
sqlite>

from what this means, I guess what I need to do is to re-arrange the 
table...




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

[sqlite] Custom API Server and custom database

[Henry Udoye]

-- 
Henry Udoye.
4165 Whiteplains Road.
P. O. Box 1290.
Bronx. N. Y. 10466
Phone : 914-316-1854.
Fax : 206-350-3803
hudoye1...@gmail.com





Can you supply or provide the following:



1.   Create custom APIs server with multiple end points and multiple
clients

2.   Build a Custom database

3.  Integrate APIs



Build a database –including enabling or integration and configuration of
APIs
Search or extract the search engines content, data, information, etc.
through or via search engines queries.
Store the results or findings of the search in the database.
Query or extract or retrieve (including query conditions, sub-query,
criteria, requirements, parameters) the database content or data for:
Affiliate programs
Information, news, current affairs or events.
Reseller programs
Store the results or findings of the search in the database for affiliate
programs
information, news, current affairs or events, reseller programs in the
database.
Query or extract or retrieve (including query conditions, sub-query,
criteria, requirements, parameters) the affiliate programs in the database
content or data for:
Affiliate products and /or services
Query or extract or retrieve (including query conditions, sub-query,
criteria, requirements, parameters) the reseller programs in the database
content or data for:
Reseller products and/or services.
Integrate, configure, connect tasks APIs.
Project APIs
Business Partners APIs include the affiliate programs. For examples Click
Bank, Warrior Plus, JVzoo etc.
Business Partners APIs include the Reseller programs. For examples
Microsoft, Hostgator, etc.
Public APIs include the social media, Twitter, WordPress etc.
Catalog of APIs:
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COLLATE doesn't work with parenthesized expressions (doc issue)

[Dan Kennedy]

On 08/17/2017 07:07 AM, Jens Alfke wrote:

On Aug 16, 2017, at 4:55 PM, Richard Hipp  wrote:

Remember:  COLLATE binds more tightly than ==.  So

 'xyz' = 'XYZ' COLLATE nocase

gets parsed out as

'xyz' = ('XYZ' COLLATE nocase)

And that expression has a different meaning from your

   ('xyz' = 'XYZ') COLLATE nocase

Oh, so collation is actually an attribute of a string value, that gets applied 
during comparisons involving that value? That’s very different from my mental 
model!

In that case, what is the meaning of
('foo' COLLATE binary) = ('FOO' COLLATE nocase)
i.e. which of the two competing collations does the = operator decide to use?

And is the collation attribute passed around with the string value, through 
intervening expressions? I.e. is this expression true?
'FOO' = ifnull(null, 'foo' COLLATE nocase)


"binary" and "yes". Full rules explained here:

  https://sqlite.org/datatype3.html#collating_sequences

Dan.



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