[sqlite] SQLite 3.6.22 ATTACH no longer works for files outside the current working directory

[Hick Gunter]

In SQLite 3.5.9 it used to be possible to write

sqlite> ATTACH '/some/unix/file/name.db' AS mydb;


In SQLite 3.6.22 this results in the error message

Error: unable to resolve operation

whereas

sqlite> ATTACH /some/unix/file/name.db AS mydb;

is a syntax error at '/'


The problem seems to be the indicated line in the function resolveAttachExpr
(located in attach.c). If the expression ALREADY IS a string, then it makes no
sense to call sqlite3ResolveExprNames() which CANNOT HANDLE a string.


static int resolveAttachExpr(NameContext *pName, Expr *pExpr)
{
  int rc = SQLITE_OK;
  if( pExpr ){
>>>>if( (pExpr->op!=TK_ID) && (pExpr->op!=TK_STRING) ){
  rc = sqlite3ResolveExprNames(pName, pExpr);
  if( rc==SQLITE_OK && !sqlite3ExprIsConstant(pExpr) ){
sqlite3ErrorMsg(pName->pParse, "invalid name: \"%s\"", 
pExpr->u.zToken);
return SQLITE_ERROR;
  }
}else{
  pExpr->op = TK_STRING;
    }
      }
  return rc;
}

Regards
Gunter Hick


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

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite 3.6.22 assert "(p->selFlags & SF_HasTypeInfo)==0" fails with virtual tables

[Hick Gunter]

Consider a query of the form

UPDATE  SET  = (SELECT  FROM  WHERE  != 
0) WHERE  = ;

If both tables are native tables, the function selectAddSubqueryTypeInfo() gets 
called exactly once for the subquery.

However, if both tables are virtual tables, the function will be called more 
than once and the assertion will fail.

It does not matter if the virtual tables are created/connected by SELECTING 
from them before the update or not.

Everything seems to be working fine if the function returns WRC_Continue 
instead of aborting when called again.



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

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with disableTerm() and virtual tables

[Hick Gunter]

I am running SQLite 3.6.22 (debugging code enabled) with extensive use of 
virtual tables that behave as if their structures were as follows (unused 
fields have been omitted):

CREATE TABLE one (
aunsigned,
bunsigned,
cunsigned,
dunsigned,
stext);

CREATE INDEX one_pk ON one (a, b, c, d);

CREATE TABLE two (
one_rowidunsigned,
bunsigned,
eunsigned,
funsigned,
gunsigned);

CREATE INDEX two_pk ON two (one_rowid);

CREATE TABLE three (
aunsigned,
bunsigned,
eunsigned,
hunsigned);

CREATE INDEX three_pk ON three (a, b, e, h);

When I attempt to perform a certain join (shown below), the disableTerm 
function fails in the ALWAYS assertion, because the wtFlags field already has 
the TERM_CODED bit set. As far as I can tell, it is looking at the first 
constraint in the ON clause of the LEFT JOIN, possibly for the second time.

The problem goes away on any of the following conditions:

-  native tables are used as opposed to virtual tables
-  the first constraint of the WHERE clause (one.a = 3) is omitted
-  the constant from the WHERE clause is repeated in the ON clause 
(three.a = 3)

I suspect there is a subtle difference in parsing and/or code generation 
between native and virtual tables the leads to this effect

SELECT
   one.s,
   two.b,
   two.e,
   two.f
FROM  one
join  two   ON two.one_rowid = one.rowid
left join three ON three.a   = one.a and
   three.b   = two.b and
   three.e   = two.e and
   three.h   = two.f
where  one.a = 3 and
   two.g = 1;




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

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Performance issue with left join in 3.24 compared with 3.22

[Hick Gunter]

Add "cross" before the first "join" to force the first table into the outermost 
loop

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Eric Grange
Gesendet: Dienstag, 26. Juni 2018 10:13
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] Re: [sqlite] Performance issue with left join in 3.24 
compared with 3.22

Also ran a few index to "force" the query plan, but with limited success:

- the "indexed by" clause does not result in the optimizer using the index 
first, it just uses the indexes in the later steps of the query plan.
- using "not indexed" still results in the same table scan of high_volume_table 
first, just without the index.
- using the unary "+" on the d table filters has no effect on the query plan 
(as these are not indexed in the first place I guess)

Using unlikely() on the d table filters seems to be the only option that works.


On Tue, Jun 26, 2018 at 10:02 AM, Eric Grange  wrote:

> Hi,
>
> I am experiencing a massive performance issue on a query with a left
> join in 3.24, now taking 40 seconds, whereas in 3.22 it was just a few
> milliseconds.
> The problematic query looks like
>
>   select d.key_field, count(*) nb
>   from low_volume_table b
>   join mid_volume_table c on c.key_b = b.id
>   left join high_volume_table d on d.key_c = c.id
>   where b.id >= $1
>   and b.filter1 = 0
>   and c.filter2 > 0
>   and d.filter3 > 0
>   and d.filter4 = 1
>   group by d.key_field
>   order by nb desc
>
> The filter fields on it are relatively non-discriminant (and
> non_indexed), however the key_field is indexed.
>
> The most discriminating conditions in this query are those on the
> low_volume and mid_volume tables, but the optimizer selects as first
> action:
>
> SCAN TABLE high_volume_table USING INDEX key_field_idx
>
> which leads to a huge number of iterations.
>
> If on the other hand, just one of the d filter conditions is removed,
> then the optimizer goes (like 3.22) first for
>
>SEARCH TABLE low_volume_table AS b USING COVERING INDEX
> low_volume_table_id_idx (b.filter1=? AND rowid>?)
>
> This happens after running ANALYZE, the sqlite1_stat for the
> high_volume table and key_field_idx is
>
>  5855234 6
>
> while for the  low_volume_table_filter1_idx it is
>
>  1976628 988314
>
> While the  low_volume_table_filter1_idx does not look very selective,
> as it is coupled with rowid filtering, it is actually very effective
> as it combines rowid & filter1, so there are just thousandths of rows
> being considered in the "group by", while when starting from a
> key_field_idx, there are millions of rows being considered, the
> overwhelming majority not fulfilling the conditions.
>
> The above table names and fields have been anonymized, if someone from
> the SQLite team want to have a look at the actual data, I can provide
> a database download (it's about 1.7 GB)
>
> Thanks!
>
>
>
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Understanding SELECT statement

[Hick Gunter]

There is no DATE datatype in SQLite. Your declaration assigns NUMERIC affinity 
for the date column.

It seems that you are storing TEXT values, which is allowed, but in conflict 
with your declaration.

Since you have not declared INTEGER PRIMARY KEY, you only achieve what UNIQUE 
alone would have done.

As SQLite is asserting a UNIQUE constraint violation, you have probably already 
inserted at least one saturday into your SchoolYearTeachingDays table. You can 
check this with:

SELECT rowid, aDate FROM SchoolYearTeachingDays WHERE aDate IN (SELECT aDate 
FROM TeachingSaturdaysInSchoolYear);

Or you may have duplicate dates (you did not declare aDate to be UNIQUE in that 
table) in your TeachingSaturdaysInSchoolYear table, which you can check with:

SELECT aDate,count() from TeachingSaturdaysInSchoolYear group by 1 order by 1;

To answer your question: INSERT INTO ... SELECT will attempt to insert each 
result row of the SELECT exactly once. If you want to insert duplicated rows of 
the SELECT only once, you need to SELECT DISTINCT.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Csányi Pál
Gesendet: Dienstag, 26. Juni 2018 15:15
An: SQlite User 
Betreff: [EXTERNAL] [sqlite] Understanding SELECT statement

Hi,

I have the 'SchoolYearTeachingDays' table with just one column, in which are 
dates:

CREATE TABLE SchoolYearTeachingDays (
aDate DATE PRIMARY KEY
UNIQUE
);
I filled it with many dates which are unique. These dates excludes dates for 
Sundays and for Saturdays. I have another, the 'TeachingSaturdaysInSchoolYear' 
table:

CREATE TABLE TeachingSaturdaysInSchoolYear (
idINT  PRIMARY KEY
   UNIQUE,
aDate  DATE,
TimetableForTheDay TEXT
);
This table holds just two dates. These two dates are for two Saturdays. On 
these two Saturdays we have to teach students. When I do the following query on 
this table, I get these two records:

2018-04-14
2018-05-05

I want to INSERT these two dates from the 'TeachingSaturdaysInSchoolYear' table 
into 'SchoolYearTeachingDays'
table.

I am trying with this query:

INSERT INTO SchoolYearTeachingDays
 SELECT aDate FROM TeachingSaturdaysInSchoolYear ; but I get this error: Error: 
UNIQUE constraint failed:
SchoolYearTeachingDays.aDate

Then I get help and this code:
INSERT INTO SchoolYearTeachingDays
 SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT IN (SELECT 
S.aDate FROM SchoolYearTeachingDays S)

It works. But I am not understanding it at all.
I wish to know followings.
How many times want to inserts the SELECT query the one of the date from the 
TeachingSaturdaysInSchoolYear table into SchoolYearTeachingDays table?

That is: the how many times wants select statement to insert one record from 
first table into second table?

--
Best, Pali
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index

[Hick Gunter]

The xBestIndex function needs to call the sqlite_vtab_collation() function to 
query the collation name required for each constraint and return the 
appropriate index number.

Subs: yes, yes, see above


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Donnerstag, 28. Juni 2018 11:00
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] order by column_name collate custom_collation, 
with virtual table index

From reading this list, I've learned that for an index to have a change to be 
used to consume an order by, the collation of the query and the index must 
match.

But in many instances, that index is one from a virtual table we implement.
So is there a way to tell SQLite that vindex is of a given custom collation, to 
open the possibility of the index being used?

FWIW, the collation is a "natural order" one, i.e. a1, a2, ..., a10, ..., a19, 
a20, ..., a100.
Right now the vindex is lexicographic, not "natural order", but of I can have 
SQLite use it somehow, I can easily change my vindex to  "natural order" too.

Subsidiary questions:
Can one have two indexes on the same column with different collations?
And thus have SQLite consider these alternate indexes depending on queries 
collations?
Which goes back to my question about how telling SQLite about a vindex's 
collation.

Thanks, --DD
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index

[Hick Gunter]

We are still using sqlite 3.7.14.1 here, so I can't verify what the current 
code does.

Support for virtual tables has been much extended, adding (OTTOMH):
- conflict resolution algorithm
- support for unchanged columns in UPDATE statements
- more detailed xBestIndex return values (# of estimated rows, UNIQUE flag)
- collation sequence support

VT implementations written before collation sequence support should be seen as 
supporting only BINARY. Since VT are by definition user defined, the same user 
has control over the queries. Changing the query to use a different collation 
sequence than the default BINARY requires changing the VT implementation to 
support that. Or at least check for BINARY and just not return that index 
number if a different sequence is required.

I cannot check what current SQLite fills into the p_idx structure in the case 
of "SELECT * FROM vt ORDER BY field(s)". Adding fields from the ORDER BY but 
leaving the "usable" bit unset would solve the problem

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Donnerstag, 28. Juni 2018 12:56
An: General Discussion of SQLite Database 
Betreff: Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, 
with virtual table index

On Thu, Jun 28, 2018 at 11:48 AM Dominique Devienne 
wrote:
>
> On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter  wrote:
>>
>> The xBestIndex function needs to call the sqlite_vtab_collation()
function to query the collation name required for each constraint and return 
the appropriate index number.
>>
>> Subs: yes, yes, see above
>
>
> Oh, great! Thanks Gunther!!!
>
> Richard, may I suggest
> https://www.sqlite.org/c3ref/vtab_collation.html
to be mentioned or linked somewhere in https://www.sqlite.org/vtab.html?
TIA. --DD

Hmmm, on second thought, https://www.sqlite.org/c3ref/vtab_collation.html
seems to work with
constraints only (i.e. WHERE clause), and not the 
sqlite3_index_info.aOrderBy[]...

The second argument must be an index into the aConstraint[] array belonging
> to the sqlite3_index_info structure passed to xBestIndex


So SQLite cannot reliably use a vindex to optimize an Order By if a custom 
collation is used in the query? Is that a latent bug?

And apparently this returns the collation of the query, instead of providing 
the vindex's "own" collation.
Which implies that it's the xBestIndex impl that supposed to rule out the 
vindex, not SQLite itself?

In other words, any xBestIndex impl  which does *NOT* call 
sqlite3_vtab_collation is necessarily buggy, if one day a query using a 
different collation (than the vindex) somehow uses a plan using that vindex?

There seems to be a lot of subtle things to consider here. Can more light be 
shed on this? In the vtab.html doc?

Thanks, --DD
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Shouldn't have to specify primary key explicitly

[Hick Gunter]

Supply a NULL for the INTEGER PRIMARY KEY to tell SQLite to "figure it out for 
yourself" (c) Siddharta Gautama "Buddha"

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Scott Robertson
Gesendet: Donnerstag, 28. Juni 2018 13:48
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Shouldn't have to specify primary key explicitly

SQLite is supposed to autoincrement by default when a column is defined as 
"INTEGER PRIMARY KEY" according to everything I've read. But I've only gotten 
this to work if I let SQLite create its own PK column. If I have an explicit PK 
column, I am expected to specify an ID myself. What am I missing? I don't know 
why I'm getting this error. Thanks.


CREATE TABLE test1 (name TEXT, date DATE);

INSERT INTO test1 VALUES ('Barney', 1999

);


SELECT * FROM test1;

name date

-- --

Barney 1999


SELECT *, rowid FROM test1;

name date rowid

-- -- --

Barney 1999 1


CREATE TABLE test2 (

id INTEGER PRIMARY KEY,

book text,

page INTEGER

);


INSERT INTO test2 VALUES (

'Fletch',

245

);

Error: table test2 has 3 columns but 2 values were supplied


INSERT INTO test2 VALUES (

1,

'Dragnet',

17

);


SELECT *, rowid FROM test2;

id book page id

-- -- -- --

1 Dragnet 17 1


INSERT INTO test2 VALUES (

'Lord of the Rings',

327

);

Error: table test2 has 3 columns but 2 values were supplied


INSERT INTO test2 VALUES (

9,

'Lord of the Rings',

327

);


SELECT *, rowid FROM test2;

id book page id

-- -- -- --

1 Dragnet 17 1

9 Lord of th 327 9


--

___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Bind Question -- Is this correct?

[Hick Gunter]

V1 uses the value bound to the SQL Variable while performing the concatenation 
of the text literal '+', the value cast as text, and the text literal 'hours'

V2 expects (wrongly) that the text literal '+:StartTimehours' magically 
replaces part of the string with the value of an SQL variable

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Stephen Chrzanowski
Gesendet: Dienstag, 03. Juli 2018 17:23
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] Bind Question -- Is this correct?

I'm attempting to do a simple bind for an integer.

The partial query is:

V1: *datetime(date(current_timestamp,'localtime'),'+'||:StartTime||'
hours')))/60) MinutesSince9*
V2: *datetime(date(current_timestamp,'localtime'),'+:StartTime
hours')))/60) MinutesSince9 *

I'm getting a failure with V2, and I'm assuming that binding isn't understood 
when the bind is within a string.  If this assumption is correct, is V1 the 
correct and safe way to bind the values?

In THIS particular case, :StartTime is an ENUM type of integers 7, 8, or 9, and 
programmatically set based on a configuration option set via a pull down menu 
within the application, which is sanitized before performing the SQL statement 
anyways.  My concern is geared towards the future when handling strings.
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] documentation for sqlite3_index_info-ídxStr typo.

[Hick Gunter]

Indeed SQLite does not care about what the VT implementations xBestIndex 
routine stuffs in there, as long as the xFilter routine can handle it. AFA 
SQLite is concerned, this is an opaque value of pointer size.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Ben Manashirov
Gesendet: Dienstag, 03. Juli 2018 02:47
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] documentation for sqlite3_index_info-ídxStr typo.

https://www.sqlite.org/c3ref/index_info.html

Instead of referring to idxStr there is text that talks about idxPtr. It should 
mention if idxStr must point to a valid string or can it point to arbitrary 
data. If the latter than idxStr type should change to void* perhaps. Reading 
this doc it sounds like idxStr can point to anything and sqlite will treat it 
as arbitrary data instead of a string.


Happy Canada Day :)
Ben Manashirov
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] What happens when a call contains two SQL statement

[Hick Gunter]

Why should a failure in transaction #2 rollback transaction #1?

If you want this behaviour, do "begin; delete ...; insert ... on conflict 
rollback; commit;" to make both statements run in one transaction

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Cecil Westerhof
Gesendet: Montag, 09. Juli 2018 08:21
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] What happens when a call contains two SQL statement

I am working with Tcl. The best is of-course a general answer, but if it is 
depending on the used language I will be satisfied with the Tcl answer. ;-)

Say I have the following code:
set SQLCmd "
DELETE FROM testing
WHERE  key = 12
;
INSERT INTO testing
(key, value)
VALUES
(12, 'Just some text')
"
db eval ${sqlCmd}

If the insert goes wrong, will the delete be rolled back, or not?

I could use INSERT OR REPLACE, but the above code would be database independent.

--
Cecil Westerhof
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] What happens when a call contains two SQL statement

[Hick Gunter]

Only the "on conflict" clause

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Cecil Westerhof
Gesendet: Montag, 09. Juli 2018 08:38
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] What happens when a call contains two SQL 
statement

2018-07-09 8:28 GMT+02:00 Hick Gunter :

> Why should a failure in transaction #2 rollback transaction #1?
>

​I was thinking that, but was not sure. I thought that maybe everything in a 
call would be seen as a transaction. But that is not the case then: every 
statement in a call is its own transaction.

​​


> If you want this behaviour, do "begin; delete ...; insert ... on
> conflict rollback; commit;" to make both statements run in one
> transaction
>

Is this standard SQL, or SQLite specific?



> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Cecil Westerhof
> Gesendet: Montag, 09. Juli 2018 08:21
> An: SQLite mailing list 
> Betreff: [EXTERNAL] [sqlite] What happens when a call contains two SQL
> statement
>
> I am working with Tcl. The best is of-course a general answer, but if
> it is depending on the used language I will be satisfied with the Tcl answer.
> ;-)
>
> Say I have the following code:
> set SQLCmd "
> DELETE FROM testing
> WHERE  key = 12
> ;
> INSERT INTO testing
> (key, value)
> VALUES
> (12, 'Just some text')
> "
> db eval ${sqlCmd}
>
> If the insert goes wrong, will the delete be rolled back, or not?
>
> I could use INSERT OR REPLACE, but the above code would be database
> independent.
>

--
Cecil Westerhof
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] How to insert this string?

[Hick Gunter]

Insert into sql_replace values ('xxx','''A'',''B'',''C''');

Note: All single quotes. To include a single quote in a single quoted string, 
it needs to be doubled.

Not sure what you are trying to do here. Build SQL queries on the fly?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Bart Smissaert
Gesendet: Freitag, 27. Juli 2018 10:38
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] How to insert this string?

Have a  table like this:

create table sql_replace(string_old text, string_new text)

The idea is to do automatic string replacements in SQL.
Say we have this SQL:

select * from table1 where a in(xxx)

then after the string replacement it should be this:

select * from table1 where a in('A', 'B', 'C')

Having some  trouble inserting the 2 strings, xxx and 'A', 'B', 'C'
in the table. Tried various constructions, but getting errors. I can see this 
will be a lot easier to do in code with place holders and bound values, but in 
this case I need to do it with direct SQL in an Android phone app.

Any advice how to do this in direct SQL?

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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Sqlite Sharding HOWTO

[Hick Gunter]

We almost exclusively use virtual tables in our application, and this includes 
virtual table code to access Faircom CTree files and in-memory data 
dictionaries. The structure (fields, indexes) of these tables is fixed (and 
identical for corresponding CTree and DD tables), with sharding achieved by 
"cloning" the common structure into separate tables, with the "clone 
parameters" that describe the value(s) of certain key field(s) being present in 
the name. E.g. the table named customer_PA would contain only customers from 
Pennsylvania. Creating a simple view (select * from customer_PA union all 
customer_NY ...) has the drawback of acessing all member tables, even if the 
constraints would require searching only one table. It also requires that all 
tables be contained in the same database.

Our solution is a "partition" provider that knows about "member tables" and 
"clone parameters" and can handle "partition constraints" as well as ordered 
(merge) and unorded (sequential) retrieval. The name of the "partition" table 
does not include any "clone parameters" (e.g customer).

So "SELECT * FROM customer;" will internally do "SELECT * FROM customer_NY;" 
followed by "SELECT * FROM customer_PA;" because the member table has 2 entries 
('customer','customer_NY'), ('customer','customer_PA').

But "SELECT * FROM customer WHERE ... state = 'NY';" would determine that the 
"clone parameter" state only matches table customer_NY and therefore only query 
that table.

And "SELECT * FROM customer ... ORDER BY name;" would prepare identical 
statements against both tables, fetch a record from each and return the 
"smaller" one (because the virtual table supports indexing by name, the 
xBestIndex method can tell SQLite that it can handle this kind of query and 
sets the "orderByConsumed" flag; if the ODER BY expression cannot be handled 
via an index, it goes back to sequential execution and lets SQLite do the 
sorting). An n-way merge is implemented as a binary tree to minimize 
comparisons.

A smilar approach may be possible with native tables that reside in different 
native database files (limited by the maximum number of concurrently attached 
databases).


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Gerlando Falauto
Gesendet: Sonntag, 29. Juli 2018 10:34
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Sqlite Sharding HOWTO

Hi,

I'm totally new to sqlite and I'd like to use it for some logging application 
on an embedded linux-based device.  Data comes from multiple (~10), similar 
sources at a steady rate.
The rolling data set would be in the size of 20 GB. Such an amount of storage 
would suffice to retain data from the previous week or so.

Reading the documentation https://www.sqlite.org/whentouse.html somehow 
suggests the usage of sharding:

>Concurrency is also improved by "database sharding": using separate
database files for
> different subdomains. For example, the server might have a separate
SQLite database for each
> user, so that the server can handle hundreds or thousands of
> simultaneous
connections, but
> each SQLite database is only used by one connection.

In my case I would be doing sharding on the data source and/or the day of the 
timestamp, so to have individual files in the size of a few hundreds MB.
This way, deleting the oldest data would be as easy as deleting the 
corresponding file.

However, I did not find any reference whatsoever on sharding being available 
_within_ sqlite.
Ideally, I would like to have a way of "seeing" the whole dataset with a single 
query spanning all  available databases.

Would that be at all feasible? I saw the "attach database" statement which 
seems closely related but whose use-case I honestly don't get.
If not, is there any state-of-the-art adapter layer that would be performing 
(and hide) the underlying sharding? I don't really care about query performance 
(e.g. if such a global query spanning 20 different databases is indeed 
performed serially, thereby take 20 times longer), I just need a way of hiding 
this detail.

I saw some reference to SPHiveDB
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg43575.html
but the project looks stale (9 years since the last commit).

I also looked into AtomDB but it looks overly complicated for my use-case 
(single, embedded server), plus it somehow requires the underlying sharding to 
be totally exposed.

Any ideas?
Gerlando
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
__

Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex/xFilter question

[Hick Gunter]

For your JOIN case, I would expect the following "conversation" (simplified and 
based on the original cost based QP, the NGQP may be doing something else)

xBestIndex( table A, no constraints) -> Full table scan FTS(A), cost = CARD(A), 
count=CARD(A)
xBestIndex( table A, OID) -> primary key lookup PK(A), cost = LOG(CARD(A)), 
count=1, unique flag set
xBestIndex( table B, no constraints) -> FTS B
xBestIndex( table B, Owner) -> partial table scan PTS(B, Owner); since this 
seems to be unsupported, this is also FTS(B)

Now it considers the possible query plans

Lookup in table A, followed by a full table scan of table B: LOG(CARD(A)) * 
CARD(B) (because there is no index on B.Owner)
Full table scan of table B, followed by lookup in table A: CARD(B) * 
LOG(CARD(A))

Since the cost estimate is the same, it is free to choose either plan, 
apparently the second one. In both cases, a full table scan of table B is 
performed. Why does this cause a problem in the second query plan?

Note: you can use the keyword "cross" to force the desired order. Does your 
query work then? What is the difference in the way that table B is handled?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von David Fletcher
Gesendet: Sonntag, 29. Juli 2018 18:42
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] virtual tables, xBestIndex/xFilter question

First, thanks in advance for any help offered.

I'm pretty comfortable using sqlite but just now starting to develop with 
virtual tables.
I'm running into troubles and I'm pretty sure it's because my mental model of 
sqlite is wimpy.

I'm trying to build a tool that interfaces to C++ objects in memory that are 
basically arranged as a tree (or graph or mesh).  For example, let's suppose I 
have a class called A and objects of this class can own any number of B 
objects.  Every A and B object has an ObjectID.  Each object also has an owner.

In C/C++ I would do this sort of thing:

 A* pA = GetOjectGivenID(12345);  B* pB;
 BIterator iter(pA);

 while ((pB = iter.Next()) != 0) {  assert(pB->pOwner == pA); }

That's simple enough.  I'm trying to achieve the same effect using virtual 
tables, each of which follow this basic pattern:

 create table A(OID integer primary key, Owner integer, Attr1 integer, Attr 
text, ...) without rowid;

That is, the first column of every table is OID, the object ID.  For most of 
the tables, there's also a column called Owner and maybe other columns that act 
as foreign keys, too.  (I haven't actually gone to the trouble to denote the 
columns are foreign keys just yet.)

Everything works when I have just a single table with SQL like this:
select * from A where A.OID == 12345;
The statement will walk through all of the rows.  Behind the scenes, a
C++ iterator is
doing all of the work.

I'm struggling with joins.  This statement doesn't work in my application:
 select * from A join B on A.OID == B.Owner where A.OID == 12345;

The xBestIndex function is called a couple of times if this is the first time 
the table has been defined.  (xBestIndex is called fewer times if the table has 
been seen before.  I'm guessing sqlite is caching some info.)  When the xFilter 
function is called, it's being handed the virtual table for B, not A. Because 
there's no object ID to act as the iterator's source, the xFilter function ends 
in failure.
I'm struggling to find aConstraintUsage settings that will cause A, the 
left-hand table in my mental model, to be presented before B.

I'm setting the estimated number of rows correctly, I think.  I think the 
argvIndex values are being set correctly, too, I think.  At least sqlite isn't 
complaining about malformed expressions.  But, something is missing.

I spent some time looking at other examples but most of them seem to use other 
tables within sqlite to hold the data vs. objects in memory. I did stumble 
across https://osquery.io/  yesterday, which looks interesting and useful.  
It's also somewhat complex and I haven't delved into its xBestIndex/xFilter 
implementations just yet.

I think I'd benefit from looking at any application that uses C/C++ objects in 
memory plus iterators to traverse objects in a tree/graph/mesh/etc.  Can anyone 
point out projects like this?

I think I'd also benefit with documentation that shows more of what's happening 
behind the scenes with xBestIndex/xFilter.  Can anyone point me to 
documentation that is more detailed?  I've looked at the sqlite docs, Jay 
Kreibich's book& Mike Owens book and "Query Anything" documentation.

Thanks,

David


___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG 

Re: [sqlite] [EXTERNAL] Re: Very, very slow commits - Possibly solved

[Hick Gunter]

Based on the currently available information I woudl suggest the following 
schema:

CREATE TABLE IF NOT EXISTS "Disruptions" (
 "id" INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT,
 "version" integer NOT NULL,
 "Disruption_id" INTEGER NOT NULL,
 "status" integer NOT NULL,
 "category" integer NOT NULL,
 "subCategory" integer NOT NULL,
 "location" integer NOT NULL,
 "levelOfInterest" integer NOT NULL,
 "corridor" integer NOT NULL,
 "severity" integer NOT NULL,
 "startTime" TEXT NOT NULL COLLATE NOCASE,
 "comments" integer NOT NULL,
 "currentUpdate" integer NOT NULL,
 "CauseAreaPointX" real NOT NULL,
 "CauseAreaPointY" real NOT NULL,
 "endTime" text NOT NULL COLLATE NOCASE,
 "remarkTime" TEXT NOT NULL COLLATE NOCASE,
 "lastModTime" TEXT NOT NULL COLLATE NOCASE,
 "Direction" TEXT COLLATE NOCASE
);

CREATE INDEX "Disruptions_Idx1" ON Disruptions 
("location","corridor","status","category","severity","levelOfInterest","version","subCategory","startTime");

CREATE UNIQUE INDEX "Disruptions_Idx2" ON Disruptions 
("Disruption_id","version","category","subCategory");

CREATE UNIQUE INDEX "Disruptions_Idx3" ON Disruptions 
("version","Disruption_id","location");

CREATE INDEX Disruptions_Idx5 ON Disruptions 
("status","Disruption_id","Severity","levelOfInterest","category","subCategory","version");

Remarks:

COLLATE is only required for text values. If you always store data of the 
declared type, COLLATE has no business with a non-text column.

I have reordered the fields so that fields used in one or more indexes are at 
the front of the record. This allows SQLite to quit decoding the record faster 
when building index strings. I have assumed that each index is used/updated 
equally often; if you have an index that is used most, reordering the fields 
may help processing speed.

Non-index fields should be ordered so that fields that feature prominently in 
the retrieved data come first; again, this will allow SQLite to quit decoding 
the record earlier when reading data.

It may also improve performance to create a "covering index" by adding the (one 
or two) fields retrieved to the index used for locating the record. This allows 
SQLite to retrieve these fields directly from the index BTree without referring 
back to the table BTree.

I assume the order of the fields of each index matches the order of the fields 
in the ORDER BY clause(s) of the queries that use the respective index.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Rob Willett
Gesendet: Dienstag, 31. Juli 2018 16:31
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Very, very slow commits - Possibly solved

Dear all,

We think we have now found the issue with the slow commits.

We believe this is due to an inherent (and old) defect in our database design. 
We think our original design has an implicit ordering of rows in a table, when 
the table is only increasing this flaw in the design isn't apparent.

However when we started deduping the table AND we copied rows from one table to 
another to move things around, we changed the underlying order of rows. Sqlite 
handles the design change BUT the flaw in our design becomes apparent as we 
keep moving the data around and data gets mixed up. The database slows down 
when we create a second table with an identical structure to the first table, 
copy the data into the new table, drop the old and then when we rename the old 
table to the new table, things appear to slow down. Logically speaking SQLite 
shouldn't notice the difference in row order, but things do slow down, even 
with analyse.

We think that a better index definition could solve the problem for us, a 
better database design would, but thats a tricky problem.

We're now going back to our 60GB database and start from scratch to see if we 
can create the issue (now we think we know what it is).

Thanks to everybody who contributed ideas, we appreciate the help.

Rob

On 31 Jul 2018, at 15:19, Rob Willett wrote:

> Simon,
>
> As an exercise we have just added in COLLATE NOCASE to our integer
> columns.
>
> Whoops! We thought this would make no difference but its added extra
> 70% to our processing speeds.
>
> We've now got to the stage where we can make changes quickly, so we'll
> back that change out and go back to the integer defn without COLLATE
> NOCASE.
>
> Rob
>
> On 31 Jul 2018, at 14:59, Rob Willett wrote:
>
>> Simon,
>>
>> Apologies for taking so long to get back, we've been building a test
>> system and its taken a long time.
>>
>> We're just getting round to trying your ideas out to see what
>> difference they make,
>>
>> We've created a new table based on your ideas, moved the collate into
>> the table, analysed the database. We did **not** add COLLATE NOCASE
>> to the columns which are defined as integer

Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading

[Hick Gunter]

Are you sure it is not the special case documented in the ATTACH command? (see 
https://sqlite.org/lang_attach.html)

" Transactions involving multiple attached databases are atomic, assuming that 
the main database is not ":memory:" and the journal_mode is not WAL. If the 
main database is ":memory:" or if the journal_mode is WAL, then transactions 
continue to be atomic within each individual database file. But if the host 
computer crashes in the middle of a COMMIT where two or more database files are 
updated, some of those files might get the changes where others might not."

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von sanhua.zh
Gesendet: Mittwoch, 01. August 2018 09:57
An: sqlite-users ; drh 
Betreff: [EXTERNAL] [sqlite] UNION ALL bug in Multi-threading

I find a bug that exists in `UNION ALL`.


tldr: `UNION ALL` will not merge the data in different schemas in the moment 
that one of schema is committed but the another not. BUT, `UNION` will.


Here are the reproduce steps:


Preparation:
1. Prepare a database named "OLD"
1.1 Create a table for "OLD": `CREATE TABLE old(i INTEGER)`
1.2 Insert values into "OLD" to make it large enough: `INSERT INTO old 
VALUES(?1)` 2. Prepare a database named "NEW"
2.1 Create a table for "NEW": `CREATE TABLE new(i INTEGER)`


Migration:
For thread 1:
1. Attach "OLD" to "NEW" as "oldSchema": `ATTACH OLD AS oldSchema` 2. Migrate 
data from "OLD" to "NEW" in same transaction. Note that they should be executed 
with same handle using ATTACH mentioned in 1.
2.1 `BEGIN IMMEDIATE`
2.1 Select one of the row from "OLD": `SELECT i FROM oldSchema.old`
2.2 Insert the row into "NEW": `INSERT INTO main.new VALUES(?1)`
2.3 Delete the row from "OLD": `DELETE FROM oldSchema.old WHERE i == ?1`
2.4 `COMMIT`


For thread 2-N:
1. Create a view that union two tables: `CREATE TEMP VIEW v AS SELECT i FROM 
oldSchema.old UNION ALL SELECT i FROM main.new` 2. Select one of the value from 
view: `SELECT i FROM temp.v ORDER BY i LIMIT 1 OFFSET ?1`.
Here is the strange result:
As an example, if the values of 0-999 is inserted into "OLD", then value N 
should be selected as expected at offset N.
But in these kind of steps, it will not.


It can be a little bit hard to reproduce due to the multi-threading. BUT if it 
sleeps for a while when committing, it will be much easier to reproduce:
// vdbeCommit method of vdbeaux.c
for(i=0; rc==SQLITE_OK  idb-nDb; i++){
 Btree *pBt = db-aDb[i].pBt;
 sqlite3_sleep(10); // additional sleep here  if( pBt ){
  rc = sqlite3BtreeCommitPhaseOne(pBt, 0);  } }


It seems that the bug happens when one of the schema is committed but the 
another one is not.
On the other handle, if `UNION ALL` is changed to `UNION` while creating view, 
the bug will not happen too.
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading

[Hick Gunter]

UNION ALL will just return whatever both sides produce, irrespective of 
duplicates, whereas
UNION will return only 1 copy of duplicated records.

asql> select 1 as x union select 1 as x;
x
-
1
asql> select 1 as x union all select 1 as x;
x
-
1
1attacho

So depending on the relative positions of the read and write transactions and 
the order of commits, the query will see either one (before or after both 
commits), two (NEW commits before OLD) or even zero (OLD commits before NEW) 
copies of any given record.

My guess is that NEW always commits before OLD and so UNION ALL is sometimes 
returning 2 copies of one record. I also expect that changing the order of the 
databases (i.e main database is OLD and NEW gets attached) will have 1 record 
missing both in UNION and UNION ALL.

Additionally, the query has an ORDER BY clause that requires sorting, which 
drastically changes the query plan.

asql> create table t1 (i integer);
asql> create table t2 (i integer);
asql> create view  un as select * from t1 union select * from t2;
asql> create view  ua as select * from t1 union all select * from t2;
asql> .explain
asql> explain query plan select * from un order by 1;
sele  order  from  deta
  -    
2 0  0 SCAN TABLE t1 (~100 rows)
3 0  0 SCAN TABLE t2 (~100 rows)
1 0  0 COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
0 0  0 SCAN SUBQUERY 1 (~200 rows)
0 0  0 USE TEMP B-TREE FOR ORDER BY
asql> explain query plan select * from ua order by 1;
sele  order  from  deta
  -    
1 0  0 SCAN TABLE t1 (~100 rows)
1 0  0 USE TEMP B-TREE FOR ORDER BY
2 0  0 SCAN TABLE t2 (~100 rows)
2 0  0 USE TEMP B-TREE FOR ORDER BY
0 0  0 COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Mittwoch, 01. August 2018 11:34
An: General Discussion of SQLite Database 
Betreff: Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading

On Wed, Aug 1, 2018 at 10:39 AM Hick Gunter  wrote:

> Are you sure it is not the special case documented in the ATTACH command?
> (see https://sqlite.org/lang_attach.html)
>

Good point. OP will tell us if it applies to his/her case.


> " Transactions involving multiple attached databases are atomic,
> assuming that the main database is not ":memory:" and the journal_mode is not 
> WAL.
> If the main database is ":memory:" or if the journal_mode is WAL, then
> transactions continue to be atomic within each individual database file.
> But if the host computer crashes in the middle of a COMMIT where two
> or more database files are updated, some of those files might get the
> changes where others might not."
>

Still, why would UNION behave differently from UNION ALL? That's the puzzling 
part IMHO. --DD ___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

[Hick Gunter]

Judging from the following output, I would say it is data producing, but 
returns no rows for no table or a table that has no indexes. Just because a 
given select statement returns no matching rows does not make it invalid

asql> create temp table test (i integer, t text);
asql> create index test_i on test(i);
asql> pragma index_list(test);
seq   name   uniq
  -  
0 test_i 0
asql> explain pragma index_list(test);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Integer0 1 000  NULL
1 String80 2 0 test_i 00  NULL
2 Integer0 3 000  NULL
3 ResultRow  1 3 000  NULL
4 Halt   0 0 000  NULL

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von David Raymond
Gesendet: Mittwoch, 01. August 2018 16:31
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Is pragma index_list without supplied table 
name valid SQL?

Don't forget this point about pragmas:

https://www.sqlite.org/pragma.html
"No error messages are generated if an unknown pragma is issued. Unknown 
pragmas are simply ignored. This means if there is a typo in a pragma statement 
the library does not inform the user of the fact."

That way if there's a typo, or if you try a new pragma in an old version then 
it won't complain, it just won't do anything.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Bart Smissaert
Sent: Wednesday, August 01, 2018 10:13 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Is pragma index_list without supplied table name valid SQL?

Using SQLite 3.22.0

In my app I have code to determine if a given SQL string is data-producing, 
non-data producing or invalid. It uses these 3 SQLite functions:

sqlite3_prepare_v3
sqlite3_stmt_readonly
sqlite3_column_count

Have been using this code for a few years and sofar never failed, but now come 
across:
pragma index_list
so, without a supplied table name.
This gives me data-producing and I think it should give me invalid.

Firstly is this SQL indeed invalid?

Secondly, if it is I need to add some code to pick this up and was thinking 
about using explain for that. Explain pragma index_list gives me:

addr opcode p1 p2 p3 p4 p5 comment
---
0 Init 0 1 0  00 Start at 1
1 Halt 0 0 0  00

And that to me looks it is indeed an invalid SQL as it gives a Halt already in 
the second row and produces no further rows.

Am I right here and would this be a good way to pick up invalid SQL?


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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Common index for multiple databases

[Hick Gunter]

This is what I think you are asking:

- You have a "main office" computer that holds the current information on 
"everything"
- You have several different categories of users (technicians, accountant, ...) 
that require different subsets of the data
- Each user has his own computer, that may be disconnected from the "main 
office", e.g. for "field work"
- When a user's computer is "attached" to the "main office", it needs to be 
"synchronized".

If this is correct, then you require either a "distributed" DBMS that handles 
synchronization by itself, or you need to do some programming both inside and 
outside  of SQLite.

This may be appropriate for you:

- As already stated, SQLite has just 1 file to hold all tables and indexes of 
the schema. Make this identical for all users. You can always leave the tables 
empty with just minimal overhead.
- Downloading from "office" to "user" is accomplished by using ATTACH to make 
the "user" and "office" databases accessible. Just run the appropriate INSERT 
... INTO statements. Check the authorizer callback to allow different users to 
access only the tables/fields that they are allowed to see. Limiting the rows 
requires an appropriate WHERE clause.
- "Work" done by the user while offline needs to be saved in a worklog table.
- Uploading the "work" of a user would copy the new worklog records into the 
"office" worklog table, just another INSERT ... INTO, to be processed by a 
dedicated sync application.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von John R. Sowden
Gesendet: Donnerstag, 02. August 2018 19:12
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Common index for multiple databases

I have been reviewing sqlite for a couple of years, but still use foxpro.  I 
have a question regarding an index issue.

Currently I have several types of databases (in foxpro, one per file) that all 
point to an index of a common field, a customer account number.  The databases 
are for accounting, technical, general info lookup, etc.  \

I do not want these databases to all reside in one sqlite file.  How do I index 
each database on this customer account number when each database and associated 
index are in separate files?  Is this what seems to be referred to as an 
external file?  I assume that I would have to reindex each database each time 
it is opened, since a record could have been edited, etc.

tia,

John



___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole file?

[Hick Gunter]

Please try to avoid using keywords as names, especially if they conflict with 
the intended datatype. "text blob not null" creates a field of name "text" 
whose content is a blob and yet you intend to store text data (with embedded 
newlines) in it.

If you store the lines separately, you can always group_concat() them together 
on retrieval.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Abramo Bagnara
Gesendet: Freitag, 03. August 2018 21:04
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Save text file content in db: lines or whole file?


I need to save text files (let say between 1 KB to 20 MB) in a SQLite DB.

I see two possibilities:

1) save all the content in a single column:

create table content(id integer not null primary key,
 text blob not null);

2) split the content in lines:

create table line(content integer not null,
  line integer not null,
  text blob not null,
  primary key(content, line));

Some queries will need to extract the whole file, while other queries will need 
to extract the text for a range of lines.

According to your experience it is better/faster the first option, the second 
option or a smarter option I've not considered?

My partial considerations are:

- 1 is simpler
- 1 leads to faster load
- 1 is slower to retrieve a range of lines (not 100% sure)

--
Abramo Bagnara
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole file?

[Hick Gunter]

Good luck with quoted speech that contains more than one sentence. E.g.

William Faulkner said, “Never be afraid to raise your voice for honesty and 
truth and compassion against injustice and lying and greed. If people all over 
the world...would do this, it would change the earth.”

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von R Smith
Gesendet: Montag, 06. August 2018 16:20
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole 
file?

On 2018/08/06 12:00 PM, R Smith wrote:
>
>> I need to save text files (let say between 1 KB to 20 MB) in a SQLite
>> DB.
>>
> Why not do both?
>
> If it was me, I would write some code to split the text into sentences
> (not lines - which is rather easy in English, but might be harder in
> some other languages).
//...

I've received two off-line questions as to how I could parse text into 
sentences in "English" even, and thought I would reply here since it might 
clear up the confusion for others too.

The said questions indicated that the authors probably imagined me possessing 
some fancy AI comprehending the language into what constitutes notional 
sentences (Subject+Predicate) or such, but I fear the meaning was much more 
arbitrary, based on common syntax for written English - as William Faulkner 
wrote in a letter to Malcolm Cowley:

*"I am trying to say it all in one sentence, between one Cap and one
period."*


Think of paragraphs in English as large records delimited by 2 or more
Line-break characters (#10+#13 or perhaps only #10 if on a *nix
platform) between texts.

Each paragraph record could be comprised of one or more sentences (in
English) as records delimited by a full-stop+Space or
full-stop+linebreak, or even simply the paragraph end.

By these simple rules, the following can easily parsed into 1 paragraph
with 2 sentences and a second paragraph with 1 sentence (lines here used
as formatting only, actual line-breaks indicated with "<-" marker):
<-
The quick brown fox jumps over the
lazy dog.  My grandma said to your
grandma, I'm gonna set your flag
on fire.<-
<-
Next paragraph here...<-
<-

Now a more difficult paragraph would be a the following, all of which
would translate in to 1 single sentence if only the above rules are
catered for:
<-
I have three wishes:<-
   - to be outlived by my children<-
   - to fly in space once before I die<-
   - to see Halley's comet once more<-
<-

That will be a single-sentenced paragraph.  It's up to the
end-implementation to gauge whether that would be sufficient a split or
not.

To put this into a DB, I would strip out the line-breaks inside
sentences (perhaps not strip out, but replace with space characters,
much like HTML does) to make them more easily handled as "lines". The
final DB table might then look like this:

ID |  fileID | parNo | parLineNo | docLineNo | txtLine
  1 | 1   |   1   | 1 | 1 | The quick brown fox
jumps over the lazy dog.
  2 | 1   |   1   | 2 | 2 | My grandma said to your
grandma, I'm gonna set your flag on fire.
  3 | 1   |   2   | 1 | 3 | Next paragraph here...
  4 | 1   |   3   | 1 | 4 | I have three wishes: -
to be outlived by my children - to fly in space once before I die - to
see Halley's comet once more

So yes, not a perfect walk-in-the-park, but easy to do for basic text
parsing.
Stating the obvious: If the intent is to re-construct the file 100%
exact (so it scores the same output for a hashing algorithm) then you
cannot strip out line-breaks and you need to carefully include each and
every character byte-for-byte used to split paragraphs and the like. It
all depends on the implementation requirements.

The above text format should hold for 99.9% of English literature text
that can be had in text files (i.e. no images, tables, etc.). Not so
easy for scientific papers, research material, movie scripts and a few
others.

Sorry for not presenting that great AI solution.  :)
Ryan

___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Database locks

[Hick Gunter]

Your impression is only correct if you are running in WAL journal_mode. In all 
other modes, readers will block the writer(s) and yo need to set a busy timeout 
or a busy handler.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Gerlando Falauto
Gesendet: Dienstag, 07. August 2018 13:55
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Database locks

Hi,

I'm trying to implement a logging system based on SQLite, using python3 package 
apsw.
There's one process constantly writing and another one reading.
From time to time I get an exception from the writer, complaining the database 
is locked.
I'm pretty sure there's no other process writing, and I was under the 
impression that readers should not block writers, they should just see a 
previous version of the dataset.
Is my assumption wrong?
The package I'm using is based on Sqlite version 3.9.2, so perhaps it's a bit 
outdated.

Could anyone please shed some light on this topic?
Thank you!
Gerlando
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Can date('now') fire a trigger automatically after a day passed?

[Hick Gunter]

Maybe you are looking for

coalesce(EndDate,datetime('now'))

This will pretend that a NULL EndDate is always seen as the 'current datetime'. 
And you can find those records that need acting upon by the constraint EndDate 
IS NULL, run whenever your application feels like it needs to "do something".

Note that the notion of 'current datetime' remains constant only within a 
single call of sqlite3_step()

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von GB
Gesendet: Donnerstag, 09. August 2018 08:47
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Can date('now') fire a trigger automatically 
after a day passed?

That would require some sort of active process or cron job. But that's out of 
scope of SQLite. You will have to implement that on your own.

But if your EndDate is always "now", why having a true column at all? If you do 
not want to take the current date from your environment, how about something 
like this:

select *, datetime("now") as EndDate from mytable

No process, no cron job required. And your EndDate will always be "now".


> Hi,
>
> I have a column in a table where a value in a row is 'now'.
> The 'now' value is in 'EndDate' column of that row.
> I have also a trigger which fire up when in the 'EndDate' column a
> Field was updated.
>
> Because the 'now' value does changing every day in sense that every
> one know that that yesterday's date is not equal with today's date I
> am thinking about that that this could be used to fire up that trigger
> automatically every day. But this does not work.
>
> Then is there a mechanism which would update automatically such date
> value - which is now the 'now' value in that 'EndDate' column Field
> after a day passed?
>
> This is needed in my application which calculates service time between
> two dates, namely between StartDate and EndDate where EndDate has the
> 'now' value.
>
> I hope I was clear what I mean.
>
> --
> Best, Pali
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] (no subject)

[Hick Gunter]

This sounds like a windows file access problem that is not letting your 
application access a file created in the context of a cmd box. Can you - from 
the sqlite shell - insert the desired record into the database? And select it 
afterwards? If so, then SQlite is working perfectly and it is your OS Setup 
that is in the way.

If you can't write to the database from the SQLite shell, then maybe your are 
in a read-only directory. Try creating a temp table (which should default to an 
in-memory database) and iserting/selecting there.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Lars Frederiksen
Gesendet: Freitag, 10. August 2018 11:42
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] [sqlite] (no subject)

Hi SQLite-users,

I have in the console prg (cmd) created a small database in SQLite with only 
one table (just in order to check out SQLite). The Table is defined like
this:

CREATE TABLE IF NOT EXISTS "Gloser"(
GRAESK VARCHAR(50) NULL,
DANSK VARCHAR(50) NULL,
LEKTION VARCHAR(10) NULL,
NOTE TEXT NULL
);

As a programming language I use Delphi 10.2 (GUI Object Pascal) and their 
FireDAC components for handling SQLite.
When I (in my Delphi program) try to write to the database like this:

fdqFlashCard.Append;
fdqFlashCard.FieldByName('GRAESK').AsString:= 'græsk'; 
fdqFlashCard.FieldByName('DANSK').AsString:= 'dansk'; 
fdqFlashCard.FieldByName('LEKTION').AsString:= 'lektion'; fdqFlashCard.Post;

Then I get this error:

[FireDAC][Phys][SQLite]ERROR: Attempt to write a readonly database

I have been searching without a result. Anybody with a helping hint?

BR Lars



___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] (no subject)

[Hick Gunter]

Sorry I don't do Windows System Management. You need to take this question to a 
windows developer support group.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Lars Frederiksen
Gesendet: Freitag, 10. August 2018 12:08
An: 'SQLite mailing list' 
Betreff: Re: [sqlite] [EXTERNAL] (no subject)

In CMD I can write to my database without problems. Can you give me a hint 
about what might be wrong with my OS-setup in Windows 10?
Thank you for the answer and your help! Much appreciated!

Best Regards
Lars


-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Hick Gunter
Sendt: 10. august 2018 11:55
Til: 'SQLite mailing list'
Emne: Re: [sqlite] [EXTERNAL] (no subject)

This sounds like a windows file access problem that is not letting your 
application access a file created in the context of a cmd box. Can you - from 
the sqlite shell - insert the desired record into the database? And select it 
afterwards? If so, then SQlite is working perfectly and it is your OS Setup 
that is in the way.

If you can't write to the database from the SQLite shell, then maybe your are 
in a read-only directory. Try creating a temp table (which should default to an 
in-memory database) and iserting/selecting there.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Lars Frederiksen
Gesendet: Freitag, 10. August 2018 11:42
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] [sqlite] (no subject)

Hi SQLite-users,

I have in the console prg (cmd) created a small database in SQLite with only 
one table (just in order to check out SQLite). The Table is defined like
this:

CREATE TABLE IF NOT EXISTS "Gloser"(
GRAESK VARCHAR(50) NULL,
DANSK VARCHAR(50) NULL,
LEKTION VARCHAR(10) NULL,
NOTE TEXT NULL
);

As a programming language I use Delphi 10.2 (GUI Object Pascal) and their 
FireDAC components for handling SQLite.
When I (in my Delphi program) try to write to the database like this:

fdqFlashCard.Append;
fdqFlashCard.FieldByName('GRAESK').AsString:= 'græsk'; 
fdqFlashCard.FieldByName('DANSK').AsString:= 'dansk'; 
fdqFlashCard.FieldByName('LEKTION').AsString:= 'lektion'; fdqFlashCard.Post;

Then I get this error:

[FireDAC][Phys][SQLite]ERROR: Attempt to write a readonly database

I have been searching without a result. Anybody with a helping hint?

BR Lars



___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] No unicode characters in sqlite3 console prg

[Hick Gunter]

SQLite will return exactly what you give it. Typically, "double converting" 
from ISO to UTF will mess up your encoding, as will attempting to display UTF 
on a console that speaks ISO.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Lars Frederiksen
Gesendet: Samstag, 11. August 2018 18:52
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] No unicode characters in sqlite3 console prg

Hello,



Is this an error of my windows 10 cmd prompt or is it a general problem that 
the sqlite3 console is not able to show unicode in the cmd-window.??

No problem writing to the database and show (greek) unicode characters in the 
cmd prompt as I key them, but when I want to show the data with the SQL SELECT 
* FROM Gloser then the greek word is shown with most of its letters as 
questionmarks.

It is not necessarily a problem as in my dbgrid of my program the unicode is 
shown correctly. I am just curious!



Best Regards Lars



___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg

[Hick Gunter]

You need to show hex() before anyone can verify what encoding is 
stored.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Lars Frederiksen
Gesendet: Montag, 13. August 2018 14:03
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] Re: [sqlite] No unicode characters in sqlite3 console prg

I use versio 3.19

Here is a print from cmd:

SQLite version 3.19.3 2017-06-08 14:26:16 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open flashcard.db
sqlite> select * from Gloser;
2|taß???a ?|taverna|[01_16]
3|taß???a ?|taverna|[01_16]
4|taß???a ?|taverna|[01_16]
5|taß???a ?|taverna|[01_16]
16|taß???a ?|taverna|[01_16]
17|taß???a ?|taverna|[01_16]
18|taß???a ?|taverna|[01_16]
19|de µ?? ?e?...|sig mig engang. (egl "du fortæller mig ikke")|[01_08]
20|d?µ?s?f?? ?/?|journalist|[01_08]
21|???µa t?|mønt, småmønt|[01_08]
22|??p??a ?|pige|[01_08]
23|µa??t??a ?|elev, skoleelev (kvindelig)|[01_08]
24|µa??t?? ?|elev, skoleelev (mandlig)|[01_08]
25|µp?? t?|flaske|[01_08]
26|?p??|(konj) som, ligesom, sådan som; eftersom|[01_08]
27|p?t??? t?|glas (Både glas og flaske er intetkøn som vin. Øl er
27|hunkøn)|[01_08]
28|p.?.= pa?ade??µat?? |f.eks. (for eksempel)|[01_08]
29|taß???a ?|taverna|[01_16]
30|taß???a ?|taverna|[01_16]
31|de µ?? ?e?...|sig mig engang. (egl "du fortæller mig ikke")|[01_08]
32|d?µ?s?f?? ?/?|journalist|[01_08]
33|???µa t?|mønt, småmønt|[01_08]
34|??p??a ?|pige|[01_08]
35|µa??t??a ?|elev, skoleelev (kvindelig)|[01_08]
36|µa??t?? ?|elev, skoleelev (mandlig)|[01_08]
37|µp?? t?|flaske|[01_08]
38|?p??|(konj) som, ligesom, sådan som; eftersom|[01_08]
39|p?t??? t?|glas (Både glas og flaske er intetkøn som vin. Øl er
39|hunkøn)|[01_08]
40|p.?.= pa?ade??µat?? |f.eks. (for eksempel)|[01_08]
41|a???|men|[01_10]
42|a???|men|[01_10]
43|a???|men|[01_10]
44|a???|men|[01_10]
45|a???|men|[01_10]
46|a???|men|[01_10]
47|a???|men|[01_10]
48|a???|men|[01_10]

The second field should show greek words but only the letters that are 
identical in danish and greek is shown. The pure greek letters like β, γ, δ, ε, 
η etc are shown as questionmarks.
Do you want me to send you the database?

Best Regards
Lars

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Rowan Worth
Sendt: 13. august 2018 03:21
Til: SQLite mailing list
Emne: Re: [sqlite] No unicode characters in sqlite3 console prg

On 12 August 2018 at 00:51, Lars Frederiksen  wrote:

> Is this an error of my windows 10 cmd prompt or is it a general
> problem that the sqlite3 console is not able to show unicode in the
> cmd-window.??
>
> No problem writing to the database and show (greek) unicode characters
> in the cmd prompt as I key them, but when I want to show the data with
> the SQL SELECT * FROM Gloser then the greek word is shown with most of
> its letters as questionmarks.
>

What version of sqlite are you using? There was significant efforts to improve 
windows console unicode handling awhile back, which I think made it in for 
3.12/3.13.

If you're still seeing issues with a recent sqlite can you provide a short 
example which reproduces the issue?
-Rowan
___
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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg

[Hick Gunter]

Select graesk, hex(graesk), dansk from gloser;

Check the hex output to see if it conforms to UTF specs; if so, then your 
console is expecting a different encoding (probably ISO). If not, then your 
data was probably entered from a source the encodes in ISO.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Lars Frederiksen
Gesendet: Montag, 13. August 2018 14:24
An: 'SQLite mailing list' 
Betreff: Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console 
prg

Hi Hick Gunter,
I do not know exactly what you mean by this (hex())?
/Lars

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Hick Gunter
Sendt: 13. august 2018 14:07
Til: 'SQLite mailing list'
Emne: Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg

You need to show hex() before anyone can verify what encoding is 
stored.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Lars Frederiksen
Gesendet: Montag, 13. August 2018 14:03
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] Re: [sqlite] No unicode characters in sqlite3 console prg

I use versio 3.19

Here is a print from cmd:

SQLite version 3.19.3 2017-06-08 14:26:16 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open flashcard.db
sqlite> select * from Gloser;
2|taß???a ?|taverna|[01_16]
3|taß???a ?|taverna|[01_16]
4|taß???a ?|taverna|[01_16]
5|taß???a ?|taverna|[01_16]
16|taß???a ?|taverna|[01_16]
17|taß???a ?|taverna|[01_16]
18|taß???a ?|taverna|[01_16]
19|de µ?? ?e?...|sig mig engang. (egl "du fortæller mig ikke")|[01_08]
20|d?µ?s?f?? ?/?|journalist|[01_08]
21|???µa t?|mønt, småmønt|[01_08]
22|??p??a ?|pige|[01_08]
23|µa??t??a ?|elev, skoleelev (kvindelig)|[01_08]
24|µa??t?? ?|elev, skoleelev (mandlig)|[01_08]
25|µp?? t?|flaske|[01_08]
26|?p??|(konj) som, ligesom, sådan som; eftersom|[01_08]
27|p?t??? t?|glas (Både glas og flaske er intetkøn som vin. Øl er
27|hunkøn)|[01_08]
28|p.?.= pa?ade??µat?? |f.eks. (for eksempel)|[01_08]
29|taß???a ?|taverna|[01_16]
30|taß???a ?|taverna|[01_16]
31|de µ?? ?e?...|sig mig engang. (egl "du fortæller mig ikke")|[01_08]
32|d?µ?s?f?? ?/?|journalist|[01_08]
33|???µa t?|mønt, småmønt|[01_08]
34|??p??a ?|pige|[01_08]
35|µa??t??a ?|elev, skoleelev (kvindelig)|[01_08]
36|µa??t?? ?|elev, skoleelev (mandlig)|[01_08]
37|µp?? t?|flaske|[01_08]
38|?p??|(konj) som, ligesom, sådan som; eftersom|[01_08]
39|p?t??? t?|glas (Både glas og flaske er intetkøn som vin. Øl er
39|hunkøn)|[01_08]
40|p.?.= pa?ade??µat?? |f.eks. (for eksempel)|[01_08]
41|a???|men|[01_10]
42|a???|men|[01_10]
43|a???|men|[01_10]
44|a???|men|[01_10]
45|a???|men|[01_10]
46|a???|men|[01_10]
47|a???|men|[01_10]
48|a???|men|[01_10]

The second field should show greek words but only the letters that are 
identical in danish and greek is shown. The pure greek letters like β, γ, δ, ε, 
η etc are shown as questionmarks.
Do you want me to send you the database?

Best Regards
Lars

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Rowan Worth
Sendt: 13. august 2018 03:21
Til: SQLite mailing list
Emne: Re: [sqlite] No unicode characters in sqlite3 console prg

On 12 August 2018 at 00:51, Lars Frederiksen  wrote:

> Is this an error of my windows 10 cmd prompt or is it a general
> problem that the sqlite3 console is not able to show unicode in the
> cmd-window.??
>
> No problem writing to the database and show (greek) unicode characters
> in the cmd prompt as I key them, but when I want to show the data with
> the SQL SELECT * FROM Gloser then the greek word is shown with most of
> its letters as questionmarks.
>

What version of sqlite are you using? There was significant efforts to improve 
windows console unicode handling awhile back, which I think made it in for 
3.12/3.13.

If you're still seeing issues with a recent sqlite can you provide a short 
example which reproduces the issue?
-Rowan
___
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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
s

Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg

[Hick Gunter]

It tells me (courtesy of findencoding.com) that the contents is encoded in one 
of

- utf-8 (most probable)
- iso-8859-10
- iso-8859-14
- iso-8859-16
- iso-20222-cn
- iso-2022-cn-ext

While your console is probably expecting either a windows or an incompatible 
ISO encoding.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Lars Frederiksen
Gesendet: Montag, 13. August 2018 14:46
An: 'SQLite mailing list' 
Betreff: Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console 
prg

Hi Hick Gunter,

This is the result:

sqlite> Select GRAESK, hex(GRAESK), DANSK from gloser;
taß???a ?|CF84CEB1CEB2CEADCF81CEBDCEB120CEB7|taverna
taß???a ?|CF84CEB1CEB2CEADCF81CEBDCEB120CEB7|taverna
taß???a ?|CF84CEB1CEB2CEADCF81CEBDCEB120CEB7|taverna
taß???a ?|CF84CEB1CEB2CEADCF81CEBDCEB120CEB7|taverna
taß???a ?|CF84CEB1CEB2CEADCF81CEBDCEB120CEB7|taverna
taß???a ?|CF84CEB1CEB2CEADCF81CEBDCEB120CEB7|taverna
taß???a ?|CF84CEB1CEB2CEADCF81CEBDCEB120CEB7|taverna
de µ?? ?e?...|CEB4CEB520CEBCCEBFCF8520CEBBCEB5CF822E2E2E|sig mig engang. (egl 
"du fortæller mig ikke") d?µ?s?f?? 
?/?|CEB4CEB7CEBCCEBFCF83CEB9CEBFCEB3CF81CEACCF86CEBFCF8220CEBF2FCEB7|journalist
???µa t?|CEBACEADCF81CEBCCEB120CF84CEBF|mønt, småmønt ??p??a 
?|CEBACEBFCF80CEADCEBBCEB120CEB7|pige
µa??t??a ?|CEBCCEB1CEB8CEAECF84CF81CEB9CEB120CEB7|elev, skoleelev (kvindelig) 
µa??t?? ?|CEBCCEB1CEB8CEB7CF84CEAECF8220CEBF|elev, skoleelev (mandlig) µp?? 
t?|CEBCCF80CEBFCF85CEBACEACCEBBCEB920CF84CEBF|flaske
?p??|CF8CCF80CF89CF82|(konj) som, ligesom, sådan som; eftersom p?t??? 
t?|CF80CEBFCF84CEAECF81CEB920CF84CEBF|glas (Både glas og flaske er intetkøn som 
vin. Øl er hunkøn) p.?.= pa?ade??µat?? 
|CF802ECF872E3D20CF80CEB1CF81CEB1CEB4CEB5CEAFCEB3CEBCCEB1CF84CEBFCF8220CF87CEACCF81CEB7|f.eks.
 (for eksempel) taß???a ?|CF84CEB1CEB2CEADCF81CEBDCEB120CEB7|taverna
taß???a ?|CF84CEB1CEB2CEADCF81CEBDCEB120CEB7|taverna
de µ?? ?e?...|CEB4CEB520CEBCCEBFCF8520CEBBCEB5CF822E2E2E|sig mig engang. (egl 
"du fortæller mig ikke") d?µ?s?f?? 
?/?|CEB4CEB7CEBCCEBFCF83CEB9CEBFCEB3CF81CEACCF86CEBFCF8220CEBF2FCEB7|journalist
???µa t?|CEBACEADCF81CEBCCEB120CF84CEBF|mønt, småmønt ??p??a 
?|CEBACEBFCF80CEADCEBBCEB120CEB7|pige
µa??t??a ?|CEBCCEB1CEB8CEAECF84CF81CEB9CEB120CEB7|elev, skoleelev (kvindelig) 
µa??t?? ?|CEBCCEB1CEB8CEB7CF84CEAECF8220CEBF|elev, skoleelev (mandlig) µp?? 
t?|CEBCCF80CEBFCF85CEBACEACCEBBCEB920CF84CEBF|flaske
?p??|CF8CCF80CF89CF82|(konj) som, ligesom, sådan som; eftersom p?t??? 
t?|CF80CEBFCF84CEAECF81CEB920CF84CEBF|glas (Både glas og flaske er intetkøn som 
vin. Øl er hunkøn) p.?.= pa?ade??µat?? 
|CF802ECF872E3D20CF80CEB1CF81CEB1CEB4CEB5CEAFCEB3CEBCCEB1CF84CEBFCF8220CF87CEACCF81CEB7|f.eks.
 (for eksempel) a???|CEB1CEBBCEBBCEAC|men a???|CEB1CEBBCEBBCEAC|men 
a???|CEB1CEBBCEBBCEAC|men a???|CEB1CEBBCEBBCEAC|men a???|CEB1CEBBCEBBCEAC|men 
a???|CEB1CEBBCEBBCEAC|men a???|CEB1CEBBCEBBCEAC|men a???|CEB1CEBBCEBBCEAC|men
sqlite>

Does it tell you whats wrong?
/Lars


-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Hick Gunter
Sendt: 13. august 2018 14:34
Til: 'SQLite mailing list'
Emne: Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg

Select graesk, hex(graesk), dansk from gloser;

Check the hex output to see if it conforms to UTF specs; if so, then your 
console is expecting a different encoding (probably ISO). If not, then your 
data was probably entered from a source the encodes in ISO.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Lars Frederiksen
Gesendet: Montag, 13. August 2018 14:24
An: 'SQLite mailing list' 
Betreff: Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console 
prg

Hi Hick Gunter,
I do not know exactly what you mean by this (hex())?
/Lars

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Hick Gunter
Sendt: 13. august 2018 14:07
Til: 'SQLite mailing list'
Emne: Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg

You need to show hex() before anyone can verify what encoding is 
stored.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Lars Frederiksen
Gesendet: Montag, 13. August 2018 14:03
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] Re: [sqlite] No unicode characters in sqlite3 console prg

I use versio 3.19

Here is a print from cmd:

SQLite version 3.19.3 2017-06-08 14:26:16 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open flashcard.db
sqlite> select * from Gloser;
2|taß???a ?|taverna|[01_16]
3|taß???a ?|taverna|[01_16]
4|taß???a 

Re: [sqlite] [EXTERNAL] Re: Unsigned

[Hick Gunter]

By definition, a pointer (the current virtual memory address of an object) only 
supports an equality test ("do these two pointers reference the same object"). 
There is no point in sorting pointers. But you can still do it by implementing 
a collating sequence which would internally do

int pointer_compare( void *a, void *b) { return (int)((uintptr-t)a - (uintptr) 
b); }

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Samstag, 25. August 2018 08:58
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Unsigned

> What is the value of a built-in UNSIGNED type when we already have INTEGER?  I
can't think of any. -- Darren Duncan

Signed integers only allow half the range of values of unsigned ones. You 
cannot store a pointer value in them. (You can by casting to signed, but then 
sorting is done wrong.)

___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Query on TEMP view.

[Hick Gunter]

In the sqlite shell, enter the .explain command and then

EXPLAIN QUERY PLAN 

for an explanation of the plan, and

EXPLAIN 

for the generated bytecode. This usually helps to understand what sqlite is 
thinking (although maybe not why).

Note that WHERE constraints are applied to the input set whereas HAVING 
constraints are applied to the output set, so perhaps changing the "outer" 
constraint to HAVING smart_search() will yield the desired effect.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Hegde, Deepakakumar (D.)
Gesendet: Dienstag, 28. August 2018 07:47
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Query on TEMP view.

Hi All,


I am facing a problem where in defined function registered to sqlite is called 
multiple time withput considering the filter.


Ex:

Table and entry:

ID  NAME  PPID

1a.mp3   2

2b.mp3   3


Query:

SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS 
TEMP WHERE smart_search(NAME,ID)


OutPut count = 1 (This is fine)


Here smart_search() is defined and given to sqlite DB.

We are expecting smart_search() to be called from sqlite only for the entry 
with PPID 2 and that will be for ID 1 a.mp3.

But we see that this is called 2 times and for both the entry.


As per my understanding. filter of PPID is added for the inner query 
smart_search() should have called only for one entry. Is this the expected 
behavior?


If i change the query as below then the smart_search() is called for only one 
entry.

SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS 
TEMP WHERE PPID=2 AND smart_search(NAME,ID)


Please help to understand on this.


Thanks and Regards

Deepak
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] .exit in script is not working

[Hick Gunter]

Try the following script:

Select "statement";
.exit
Select "after exit";

Running the script as an init file produces only the first text. An .exit 
command in an init file will only terminate the execution of the init file 
itself, not the sqlite shell as a whole. This is intended.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Stéphane AULERY
Gesendet: Freitag, 31. August 2018 19:30
An: sqlite-users@mailinglists.sqlite.org
Cc: saul...@legtux.org
Betreff: [EXTERNAL] [sqlite] .exit in script is not working

Hello,



When I use the -init option the .exit statement at the end of my script is 
never executed.



I launch sqlite from a batch file on Windows Server 2012 and Windows 7 64 bit.





Command line :



sqlite3.exe -init myscript.scr





myscript.scr :



.log sqlite.log

.bail off

.nullvalue ""



CREATE TABLE STOCKSMAG (

Article TEXT,

CodeDepot TEXT,

QteStock INTEGER,

ECclient INTEGER,

Dispo INTEGER,

ECfour INTEGER,

Proj INTEGER,

DateGen DATETIME,

SeuilReappro INTEGER

);



.import STOCKSMAG.txt STOCKSMAG







.output STOCKSCUMUL.txt



SELECT

 Article,

 SUM (QteStock) AS QteStock,

 SUM (ECclient) AS ECclient,

 SUM (Dispo) AS Dispo,

 SUM (ECfour) AS ECfour,

 SUM (Proj) AS Proj

FROM STOCKSMAG

GROUP BY Article

ORDER BY Article;



.exit





STOCKSMAG.txt :



Article|CodeDepot|QteStock|ECclient|Dispo|ECfour|Proj|DateGen|SeuilReappro

01|117|0|0|0|0|0|30/08/2018|

01|118|0|0|0|0|0|30/08/2018|

01|121|0|0|0|0|0|30/08/2018|

01|125|0|0|0|0|0|30/08/2018|



Regards,



--

Stéphane Aulery

Développeur



Service informatique

Narbonne Accessoires

04.68.44.16.54



___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Missing function sqlite3_column_index

[Hick Gunter]

The name of an output column is not even defined, much less unique, unless the 
author of the statement has done extra work (using unique column names and/or 
AS clauses).

Consider

Select a.*,b.*,c.* ...

Where each table has a column named Id. Which index would you like to have 
returned?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Sebastian
Gesendet: Sonntag, 02. September 2018 16:14
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Missing function sqlite3_column_index

Hi,
for sqlite3_bind_parameter_name there is an inverse function 
sqlite3_bind_parameter_index.
But for sqlite3_column_name, I could not find such a function.

Is it missing by intention, in order not to tempt users to call it once per 
row, which would be inefficient?
(In that case, why not just mention that in the documentation?)


Sebastian
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Can you use ORDER BY clause in aggregate functions?

[Hick Gunter]

I am not sure what you are trying to achieve. Can you give an example?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von sql...@zzo38computer.org
Gesendet: Freitag, 07. September 2018 21:30
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Can you use ORDER BY clause in aggregate functions?

Can you use ORDER BY clause in aggregate functions? It seems that you cannot; 
it is only available for window functions.
However, sometimes is useful using ORDER BY with aggregate functions that 
aren't window functions, such as GROUP_CONCAT function.
Therefore is the suggestion to add it if it doesn't already.
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Database occasionally very slow for trivial query

[Hick Gunter]

NB: SELECT COUNT() FROM  requires a traversal of the whole table 
(or index, if one exists for the field) and returns the number of non-NULL 
entries, whereas SELECT COUNT() FROM  invokes a special opcode to 
retrieve the total number of rows without actually acessing any of them and so 
is very much faster.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Joshua Watt
Gesendet: Montag, 10. September 2018 16:28
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Database occasionally very slow for trivial query

Hello,

I have seen a strange behavior when using sqlite 3.20.1, and I was hoping 
someone could help explain it. I have a database with a very simple schema:

 $ sqlite3 build/cache/bb_persist_data.sqlite3 SQLite version 3.20.1
2017-08-24 16:21:36 Enter ".help" for usage hints. sqlite> .schema CREATE TABLE 
BB_URI_HEADREVS(key TEXT PRIMARY KEY NOT NULL, value TEXT);

When our application starts up, it determines if it need to clear our the 
table. In the event that it does (which is most of the time) it uses the 
following query:

 BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT; Normally, this query 
takes no more than 1-3 seconds to complete, however, on rare occasion this will 
take an order of magnitude more
(20-30 seconds). The real kicker here, is that I am never adding any rows to 
the database (e.g. it is always completely empty), so an order of magnitude 
increase seems unnecessary to erase an already empty table. If it makes any 
difference, the actual delay occurs when the COMMIT statement is executed, the 
DELETE FROM goes pretty fast.

For reference, the following pragmas are used:

 pragma synchronous = normal; pragma journal_mode = WAL; pragma 
wal_autocheckpoint = 100;  I use the small wal_autocheckpoint because the 
database is read- mostly, and we would rather have fast readers at the expense 
of occasional slow writes. WAL mode is used because we access the database from 
multiple processes, and we need the occasional write to not block readers.

To be completely honest, this problem manifests under heavy I/O load, so I'm 
not suggesting it is necessarily sure that it is sqlite at fault, but the order 
of magnitude difference seems a bit extreme.

1) I used to use the rollback journal and didn't really see this problem, is 
there something about WAL mode that is more sensitive to I/O delay than the 
rollback journal?

2) Is there something that sqlite is doing "in the background" that might be 
making this query slow?

3) Are the some settings I could change that might make a difference?

4) Is there some sort of profiling I could enable to help pinpoint (or
confirm) that this is indeed due to I/O delay and not something internal to 
sqlite?

For reference, here is the complete log of SQL that the application executes on 
startup. Only the delay takes a significant amount of time.

 BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS BB_URI_HEADREVS(key TEXT PRIMARY 
KEY NOT NULL, value TEXT); COMMIT;  BEGIN TRANSACTION; SELECT COUNT(key) FROM 
BB_URI_HEADREVS;
COMMIT;   BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT;
Thanks all for your time,
--
Joshua Watt 
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] sqlite3_column_type returns NOT SQLITE_NULL and the following sqlite3_column_text returns with nullptr

[Hick Gunter]

You are assuming that the column is either NULL or a SQLITE_TEXT value. This 
assumption is obviously violated by the real data returned by your query (which 
you do not show).

A second possibility would be that another thread has done something to your 
prepared statement between retrieving the column type and retrieving the data.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von heribert
Gesendet: Dienstag, 11. September 2018 13:59
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] sqlite3_column_type returns NOT SQLITE_NULL and 
the following sqlite3_column_text returns with nullptr

I've a prepared select statement (the database is in :memory: based.).
After binding the input values, calling next, checking the result type of a 
text column, sometimes the follwing sqlite3_column_text returns with a nullptr.

The stange thing is: the prior called sqlite3_column_type do not return with 
SQLITE_NULL.

if  (sqlite3_column_type(_sqlite3_stmt, Field) == SQLITE_NULL)
 return false;

  LPCSTR cp = (LPCSTR)sqlite3_column_text(_sqlite3_stmt, Field); if (cp == 
nullptr) {
 ASSERT(false);
 return false;
}

Is that correct? May it be possible that sqlite3_column_type returns NOT 
SQLITE_NULL and the following sqlite3_column_text returns with nullptr?

Thx for any hint
heribert
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] The "natural" order of the query results.

[Hick Gunter]

SQLite handles GROUP BY and ORDER BY in basically the same way. If there is an 
apropirate index, then it will use this index and the rows will be returned in 
visitation order of this index. If, for exmaple by adding a new index or even 
an upgrade of the Query Planner, a different execution plan is constructed, 
then the order of the returned rows will "change".

Please note that GROUP BY id,o1,o2 and ORDER BY o1,o2 are DIFFERENT expressions 
and require reordering, which is the reason for an additional BTree step.

If you need the rows to be returned in a specific order, then you must say so 
explicitly with an ORDER BY clause; otherwise, SQLite is free to return rows in 
any order that happens to be convenient for the DB Engine. Relying on the 
"natural" order is a common way of creating code that breaks unexpectedly.

Similarly, if you need the result columns to have certain names, you must 
provide these via AS clauses.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von John Found
Gesendet: Sonntag, 16. September 2018 10:30
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] The "natural" order of the query results.

Is there some relation between the indexes used in the query, the GROUP BY 
fields used and the order of the result rows, when no "ORDER BY" clause is used?

I am asking, because I noticed, that on some queries, when I am using "ORDER 
BY" the query always use temporary b-tree for ordering, but by including the 
needed fields in the "GROUP BY" clause and removing the ORDER BY clause, the 
query returns the rows in the proper order without temp b-tree.

So, is it safe to use this implicit ordering, or this behavior can be changed 
in the future versions of SQLite?

Here is an example:

create table A (
  id integer primary key autoincrement,
  o1 integer,
  o2 integer
);

create table B (
  Aid integer references A(id),
  data text
);

create index idxA on A(o1 desc, o2 desc);

insert into A(o1, o2) values (1, 100), (2, 50), (3, 200), (5, 300); insert into 
B(Aid, data) values (1, "b"), (1, "a"), (2, "c"), (2, "d"), (2, "e"), (3, "f"), 
(3, "g");

-- Always uses temp b-tree for order by
select
  group_concat(B.data), o1, o2
from
  A
left join
  B on A.id = B.Aid
group by
  A.id
order by
  A.o1 desc, A.o2 desc;

explain query plan:
id  parent  notused detail
8   0   0   SCAN TABLE A
19  0   0   SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
58  0   0   USE TEMP B-TREE FOR ORDER BY


-- This one returns the rows in the needed order without ORDER BY select
  group_concat(B.data), o1, o2
from
  A indexed by idxA
left join B on A.id = B.Aid
group by A.id, A.o1, A.o2;

explain query plan:
id  parent  notused detail
7   0   0   SCAN TABLE A USING COVERING INDEX idxA
18  0   0   SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)


-- But if I add ORDER BY it still begins to use temp b-tree
-- regardless that it does not change the order.
select
  group_concat(B.data), o1, o2
from
  A indexed by idxA
left join B on A.id = B.Aid
group by A.id, A.o1, A.o2
order by A.o1 desc, A.o2 desc;

explain query plan:
8   0   0   SCAN TABLE A
19  0   0   SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
60  0   0   USE TEMP B-TREE FOR ORDER BY


All the above queries, returns the same result rows in the same order:

group_concat(B.data)  o1   o2
NULL   5   300
f,g3   200
c,d,e  2   50
a,b1   100




--
John Found 
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Last_row_id

[Hick Gunter]

A trigger program does not return any result rows.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Yadwindersingh
Gesendet: Sonntag, 16. September 2018 03:28
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Last_row_id

Hi all;
I am using vs15.x

Create trigger return_pono after insert on po Begin Select last_insert_rowid();

End

Trigger works quite fine in sqlite but fails to return any value to vb.net 
statement

Dim lrow as int64

Lrow = some_cmd.executescalar()


Please help
Thank you


___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Last_row_id

[Hick Gunter]

Or maybe also a SELECT ... INTO.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von David Raymond
Gesendet: Montag, 17. September 2018 16:07
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Last_row_id

I can't seem to find where/if it's mentioned, but I believe the only use of a 
select statement in a trigger is to call the raise function and trigger some 
level of error.

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


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Monday, September 17, 2018 8:06 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Last_row_id

A trigger program does not return any result rows.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Yadwindersingh
Gesendet: Sonntag, 16. September 2018 03:28
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Last_row_id

Hi all;
I am using vs15.x

Create trigger return_pono after insert on po Begin Select last_insert_rowid();

End

Trigger works quite fine in sqlite but fails to return any value to vb.net 
statement

Dim lrow as int64

Lrow = some_cmd.executescalar()


Please help
Thank you


___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] shell csv import

[Hick Gunter]

When running a script from the shell, you can redirect stderr tot he null 
device using 2>/dev/null or to the same destination as stdout using 2>&1. The 
latter is also very useful in crontab entries, as neglecting to handle stderr 
will result in an email tot he user that contains anything written there

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von D Burgess
Gesendet: Dienstag, 18. September 2018 08:15
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] shell csv import

I have a script that loads csv into an existing table.

I get this message on stderr for each row imported:

"... expected 7 columns but found 6 - filling the rest with NULL"


We have the means to send stdout to /dev/null using the .once or .output

Is there a way to send suppress stderr messages for a dot command?

If not, can we have one?
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Last_row_id

[Hick Gunter]

True, but the information available is very limited, basically just the NEW 
database row and function calls.

CREATE TRIGGER return_pono AFTER INSERT ON po BEGIN
INSERT INTO inserts VALUES 
(datetime('now'),'po','return_pono',last_insert_rowid());
END

Unless of course you start writing user defined functions that access the 
internal structures of SQLite, which seems quite beyond the current capability 
of the OP.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jean-Luc Hainaut
Gesendet: Donnerstag, 20. September 2018 10:40
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Last_row_id

On 17/09/2018 14:05, Hick Gunter wrote:
> A trigger program does not return any result rows.

True. But a "select" query in the body of a trigger can be used to evaluate a 
user-defined function (in the "where" clause for instance) in which any action 
allowed by your host language can be executed, including writing in a text file 
or opening a DB connection.

> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Yadwindersingh
> Gesendet: Sonntag, 16. September 2018 03:28
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] [sqlite] Last_row_id
>
> Hi all;
> I am using vs15.x
>
> Create trigger return_pono after insert on po Begin Select
> last_insert_rowid();
>
> End
>
> Trigger works quite fine in sqlite but fails to return any value to
> vb.net statement
>
> Dim lrow as int64
>
> Lrow = some_cmd.executescalar()
>
>
> Please help
> Thank you
>
>
> ___
> 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 | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
> 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] More bug with sqlite3_declare_vtab; also comments about ALTER TABLE

[Hick Gunter]

Why would you include clauses that modify the behaviour of *native* tables in 
the DDL that describes a *virtual* table?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von sql...@zzo38computer.org
Gesendet: Freitag, 21. September 2018 19:12
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] More bug with sqlite3_declare_vtab; also comments 
about ALTER TABLE

There seems a bug with sqlite3_declare_vtab that if you specify both INTEGER 
PRIMARY KEY and WITHOUT ROWID then it segfaults.
It is easily enough to work around, but it shouldn't segfault if the string 
pointer is a valid pointer to a null-terminated string and the database pointer 
is a valid one given to xCreate or xConnect.

Also, thank you to add "PRAGMA legacy_alter_table"; otherwise some things can 
break (including the old documentation specifying different behaviour with no 
hint that it would change).
One thing I wanted to have is to be able to use the ALTER TABLE command to 
rename views; it should not be too difficult to fix. I once fixed this myself 
actually so that ALTER TABLE could also be used to renae views, although 
perhaps it might not be thoroughly tested.

Furthermore, a documentation problem with window functions is that the none of 
lang.html, lang_expr.html, and lang_select.html mention window functions at all 
except as part of the syntax diagram in lang_expr.html (although window 
definitions are also mentioned in lang_select.html, not window functions) 
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

[Hick Gunter]

Which version are you using? The set of constraint constants was extended in 
versions 3.10 (LIKE, GLOB, REGEXP), 3.21 (NE, IS*) and most recently 3.25 
(FUNCTION)

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von dave
Gesendet: Sonntag, 23. September 2018 23:26
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] [sqlite] virtual tables, xBestIndex, 
pIdxInfo->aConstraint[].op, and 'NOT'...

Folks,

I cannot seem to find a means of filtering on negated operators, e.g. <>, not 
null, not like, etc., in the xBestIndex() method for virtual vables.  As best 
as I can tell, I cannot, unless there is something I am missing, hence this 
inquiry.

In a few virtual tables I have implemented, I have handled the 
SQLITE_INDEX_CONSTRAINT_EQ, and the SQLITE_INDEX_CONSTRAINT_LIKE (for
example) in the xBestIndex and xFilter.  These code paths are taken for queries 
of the form:

select * from myvtab where mycol = 'xxx';
select * from myvtab where mycol like 'xxx';

but /not/ for queries of the form:

select * from myvtab where mycol <> 'xxx';
select * from myvtab where mycol not like 'xxx';

I can work around these things for now with caveats in documentation, but it 
does sometimes cause confusion to users.

For example, in one case I have extended the syntax of LIKE .  That extension 
of syntax is invoked for a positive LIKE constraint, but is bypassed for a 
negated one.  I can work around that with an extension function, but I won't 
get the hints at record enumeration time that could reduce the dataset from the 
underlying source.

In other cases, I have some 'required' columns, which must be present in a EQ 
constraints (usually they wind up being parameters to a function call that 
generates the underlying data).  I emit an error when such constraints are 
missing, but it can be confusing to users when:

select * from myvtab where mycol <> 'xxx';

indicates that "you must have a constraint on 'mycol'"

Lastly, some behavioural inconsistencies occur between these forms:

select * from myvtab where mycol = null;
select * from myvtab where mycol is null;

Since the first comes in as a constraint to xBestIndex, whereas the second does 
not.

Anyway, as I said, I can work around this for now, but I thought I would ask
if:

1)  is it true:  xBestIndex doesn't get to see negated predicates, or is it 
just somewhere that I have not found?
2)  if it's not possible, would it be worthwhile to consider extending the 
operator set in some way to present the negative clauses at some release in the 
future?

Thanks for any info!

-dave

___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: storing unsigned 64 bit values

[Hick Gunter]

"unsigned" is ignored by sqlite. Depending on how you inserted the value, it 
could be stored as a text or as a real value, irrespective of the declared type.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Conor Lennon
Gesendet: Donnerstag, 27. September 2018 18:10
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] storing unsigned 64 bit values



On 27/09/18 17:03, Simon Slavin wrote:
> On 27 Sep 2018, at 11:53am, Conor Lennon  wrote:
>
>> e.g. 18446744073709551615 (one less than 2 to the power of 65)
>>
>> I seem to have managed to store this value in a database.
> What is the affiliation for that column ?  Did you declare it as INTEGER or 
> something else ?
>
> Simon.
>

It's declared as a unsigned integer

sqlite> .schema mytable
CREATE TABLE IF NOT EXISTS "mytable" (
"id" integer NOT NULL PRIMARY KEY,
"bigvalue" integer unsigned NOT NULL UNIQUE );

___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: alter table, other alter category, fails in presence of trigger on 3.25.2

[Hick Gunter]

The phrase "integer primary key" is the "wingardium leviosa" of the sqlite 
world. It must be pronouced correctly, lest you end up with a buffalo on your 
chest.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thierry Henrio
Gesendet: Donnerstag, 04. Oktober 2018 11:14
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] alter table, other alter category, fails in 
presence of trigger on 3.25.2

Hello Keith,

On Thu, Oct 4, 2018 at 1:16 AM Keith Medcalf  wrote:

>
> Unrelated, but are you sure that you want the albums "id int primary key"
> and did not happen to misspell "integer" so that the declaration
> should be "id integer primary key".  In the former case, id is an
> integer that just happens to be unique (ie, "id int primary key" is
> the same as "id integer
> unique") and not an explicitly named alias for the rowid (which
> requires correct spelling of the phrase "integer primary key").
>

I though "int" was the same as "integer" ( 
https://www.sqlite.org/datatype3.html).
Thanks.


> Second unrelated, do you not want an affinity for the album_id column
> in rates?  Should not you have declared it as "album_id integer
> references
> albums(id) on delete cascade"?
>

Correct.

Third unrelated, do not forget to create an index on the foreign key (as in
> "CREATE INDEX idxRates_album_id on rates (album_id)" for example).
>

Sure.

Fourth unrelated, do you want the title and comment_text to be case
> sensitive or should they have COLLATE NOCASE?
>

No.


> As to the issue with the updated table rename, you can either use a
> version of sqlite3 that does not have the alter table rename updates,
> or for version 3.25.2 use the pragma "PRAGMA legacy_alter_table=ON" to
> avoid using the new "change the table names in triggers etc" features
> added in
> 3.25.0 so that you can continue to use the old method of just
> "substituting tables".
>

Indeed, use the pragma, thanks! https://www.sqlite.org/pragma.html#toc

using pragma, the following script output 5, which is expected result.

drop table if exists albums;
drop table if exists rates;
--
create table albums (id integer primary key, title text, score int); create 
table rates (album_id integer references albums(id) on delete cascade, score 
int); create trigger test after insert on rates begin update albums set 
score=new.score where id=new.album_id; end;
--
insert into albums (id, title) values (1, 'Cheap Thrills');
--
begin;
pragma legacy_alter_table=ON;
drop table if exists new_albums;
create table new_albums (id int primary key, title text not null, score int); 
insert into new_albums (id, title, score) select id, title, score from albums; 
drop table albums; alter table new_albums rename to albums; pragma 
legacy_alter_table=OFF; end;
--
insert into rates values (1, 5);
select score from albums;

When I comment the pragma, I have

Error: near line 16: error in trigger test: no such table: main.albums
Error: near line 20: no such table: main.albums
Error: near line 21: no such table: albums

It is a resolution for the problem I faced : add a constraint to colum of a 
table referenced in a trigger.

Do you believe ?

a) https://www.sqlite.org/lang_altertable.html#otheralter could be updated.

b) rename A to B should not fail in the face of a trigger referencing B.

?
, Thierry
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Persistent snapshots and rollbacks

[Hick Gunter]

1) Include an "inserted at" timestamp column in each table
2) create a history table for each real table
3) use before triggers to copy the old record to the history table and set the 
"inserted at" timestamp

If you use temp triggers, you can even keep the history tables in a separate 
database from the live tables and even switch to a new history database every 
week or month or whatever schedule fits your application.

Reading the old state is just a group by primary key query over the live and 
history table. And restoring a historic state just means saving the result set 
of the old state query into the live table.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Daniel Kraft
Gesendet: Freitag, 05. Oktober 2018 17:40
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Persistent snapshots and rollbacks

Hi!

I need the ability to make multiple changes / commits to my SQLite database but 
keep snapshots of previous states and potentially roll back to those states 
later on.  All of that needs to be persistent, i.e.
survive closing the database and restarting the process.  After some time, I 
can get rid of old snapshots (my process determines by itself when and which 
snapshots can get discarded, it is not based on some fixed TTL or something 
like that).

Is it possible to do all that with SQLite?

From reading the docs, it seems to me that (persistent) WAL mode basically does 
*exactly that* internally:  Changes are recorded in the logs so that previous 
versions are retained.  Rollbacks would be possible by "simply" discarding the 
WAL entries after the desired snapshot.  And discarding of very old snapshots 
corresponds to checkpointing.

However, I'm not sure if all of that functionality is (officially) exposed to 
me as a user.  There are in particular two points where I think that my 
requirements differ from the functionality that WAL mode
exposes:

1) Handles to snapshots can be obtained and stored, but they are read-only.  It 
seems to be not possible to tell SQLite to restore the WAL to a previous 
version and then continue modifying from that version.
 (Which basically means truncating the WAL file at a certain point.)

2) From what I have seen, checkpointing can only be triggered for the full WAL 
(or whatever is possible with existing readers) and not selectively up to a 
desired point.  Of course I could work around that by creating a reader at the 
point I want to keep.  But then I wonder if it is a problem if the WAL can 
never be *fully* checkpointed (as in my requirement).  Would that mean that it 
keeps on growing forever, or is checkpointing able to remove parts from the 
beginning of the WAL?

Is my understanding here correct?  And is there some way in which I could 
achieve my requirements using WAL mode (or somehow else)?

Thank you very much!

Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou



___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Find key, value duplicates but with differing values

[Hick Gunter]

Two nested selects
The inner select groups by partId, name, value
The outer select groups by partId, name

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Donnerstag, 11. Oktober 2018 18:00
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] Find key,value duplicates but with differing values

I can find duplicates fine:

select xmd.partId, parts.title, xmd.name,
   count(*) "#dupplicates",
   group_concat(xmd.value) "values",
   group_concat(xmd.idx) "indexes"
  from extra_meta_data xmd
  join parts on parts.id = xmd.partId
  group by partId, name
 having "#dupplicates" > 1;

but most actual duplicates have the same value, so are harmless.
so I'd like to select only the xmd.name rows which have differing values.

Knowing that there can be more than 2 duplicates, i.e. a slef-join is not 
enough I think.
Any hints on how to go about this problem please? Thanks, --DD 
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Fill empty space with random

[Hick Gunter]

For an encryption scheme to suffer enigma machine type vulnerabilities, the 
concept behind it must predate WWII.

IIRC the last straw (apart from known clear text like all messages ending with 
the same greeting) the broke the enigma encoding was the fact that a radio 
operator on an italian ship was told to transmit a test message. Nobody 
provided an unencrypted text, and so he proceeded to transmit a message 
consisting of a long run of the letter L. Due to electricall constraints, the 
enigma machine never translated a character to itself - thus there was no L in 
the chipher text, which oddity was detected by the cryptanalyst, and led him to 
assume a clear text of all L.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von J Decker
Gesendet: Sonntag, 14. Oktober 2018 16:57
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] Re: [sqlite] Fill empty space with random

On Sun, Oct 14, 2018 at 7:24 AM Luuk  wrote:

> On 14-10-2018 16:17, Simon Slavin wrote:
> > On 14 Oct 2018, at 12:56pm, J Decker  wrote:
> >
> >> Is there maybe a compile option for sqlite to fill empty space in a
> >> db
> with random data rather than 0 ?
> > There is not.  But
> >
> > (A) It may be an easy change to the source code
> > (B) Your operating system may have a setting to do this
> > automatically to
> freed blocks on a storage device.
> > (C) Your device driver may have a setting to do this automatically
> > to
> freed blocks on the device.
> >
> > That type of security is normally done at OS or device level, not by
> each individual app.
> >
> > Simon.
> >
> Can you give any hints on why it would be a security issue to fill
> 'empty space' with 0, and why 'random data' should be used?
>
> ?
>
I hesitate to describe the real scenario; and want to instead manufacture one; 
but in either case I feel there will be more comments about the underlaying 
system than on Sqlite itself.

In the simple case, the VFS that the sqlite Db is mounted in is encrypted with 
a long key.  The key has cycles at 4096(A) and 16(B1-Bn) bytes
(4096/16 = 256 cycles of Bn); such that each sector is masked with A^B1(256x), 
A^B2(256x), ... all together there is no repetition because the change from Bn 
to B(n+1) at the 4096 boundary makes the stream overall appear continuously 
random.
Only data that is written is actually masked...

Sqlite likes to write 0's in large splotches (in my usage); which leaks key 
information; (only slightly more than the data stored in tables typically, 
which is a lot of the same bytes (0, 1 for instance and A-Z, a-z less-so; but 
all of that has upper bit(s) that are 0... )

And even is a specific sector (or several) is 'cracked' it doesn't do any good 
for any other page... but if LOTS of pages are found, it becomes easier to find 
what the overall A key is, which makes finding sector keys that you only need a 
few 32-64 bytes of 0's to reveal the sector specific key (for later use?)

The keys are a procedurally generated with a PRNG sha2 bit streams based; so 
512 bits (16 bytes) at a time; and sha algorithms generates VERY good PR 
numbers. which can be consumed as end-to-end bit streams.

I might look into it; there are certainly a great test suite available to 
reveal issues; but I expect Sqlite 'expects' memory to be 0 initialized (even 
when filled from disk) and that it will be a HUGE can of worms.


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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] INSERT OR REPLACE and foreign keys

[Hick Gunter]

This is the expected and documented behaviour. Maybe you are looking for UPSERT?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Roman Fleysher
Gesendet: Dienstag, 23. Oktober 2018 19:53
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] INSERT OR REPLACE and foreign keys

Dear SQLiters,

I am using INSERT OR REPLACE to update a table which holds a column which 
servers as a foreign key. But I noticed a strange behavior: If the parent 
record existed, then replace mechanism replaces it, but the records from 
children tables are deleted. The foreign key is set up to cascade on delete, as 
I think it should. So it seems that "replace" is implemented as "delete then 
insert" rather than "update". Is that normal and expected? Am I doing something 
wrong?

Thank you,

Roman
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Index help...

[Hick Gunter]

There is no datetime type in SQLite. You are storing ISO Text representations 
so you should declare the column as TEXT.

An Index is only useable for a prefix of equality contstraints followed by ONE 
inequality constraint.

From your index (model_id, confidence, ts)  and your query constraints ( '=', 
'>', 'BETWEEN') this means that model_id is useable (equality constraint) and 
confidence (inequality constraint>), which allows the Engine to locate the 
first record with model_id == 1 and confidence > 0.8 and partial scan the table 
until the model_id changes. Note that the ts field is not guaranteed to be 
ascending within this interval (a record with higher confidence but smaller 
timestamp may follow any given record in the scann portionof the table. Thus 
the ts constraint needs to be handled without the index.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Hamesh Shah
Gesendet: Mittwoch, 24. Oktober 2018 00:45
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Index help...

I need a little help with some strange indexing behaviour.

I have a table called detected.

i create a index for:
id integer, confidence ASC, timestamp ASC

Then when I query with a simple select from where with integer, then 
confidence, then timestamp in order, for some reason the timestamp index isn't 
used ?


SEARCH TABLE detected USING COVERING INDEX detected_model_id_confidence_ts 
(model_id=? AND confidence>?)


I read the website, I tried it many times around and still no joy. I can't see 
why it's not using the timestamp that is already ordered for my sql ts
> and ts < statement.



Python versions:

sqlite3.version 2.6.0 / python api version.

*sqlite3.sqlite_version 3.24.0*


table standalone:

CREATE TABLE detected ( id INTEGER PRIMARY KEY, model_id integer NOT NULL, 
state_id integer NOT NULL, dataset_id integer NOT NULL, class_id integer NOT 
NULL, confidence REAL NOT NULL, ts DATETIME NOT NULL, x0 INTEGER NOT NULL, y0 
INTEGER NOT NULL, x1 INTEGER NOT NULL, y1 INTEGER NOT NULL, file_id INTEGER NOT 
NULL )

index creation:

CREATE INDEX `detected_model_id_confidence_ts` ON `detected` ( `model_id`, 
`confidence` ASC, `ts` ASC );


I can't see the timestamp being used:

explain query plan
select distinct ts
from detected
where
model_id = 1
and
confidence > 0.8
and
ts >  '2018-10-10 01:25:25'
and
ts < '2018-10-23 08:10:17'
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] sharing in memory db through threads

[Hick Gunter]

Have you enabled URI filenames? See https://www.sqlite.org/uri.html for details

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Wasilios Goutas
Gesendet: Montag, 05. November 2018 16:28
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] sharing in memory db through threads

Hi,


I try to use an in memory database which works as long as I use as db path 
":memory:".


To speed up pre-processing and importing of data I would like to grand several 
threads access to the same in memory database and have found the documentation 
on https://www.sqlite.org/inmemorydb.html saying that this is possible by 
opening the DB with sqlite3_open("file::memory:?cache=shared", &db) by each 
thread.


Unfortunately instead of being in memory, this creates a file on my Ubuntu box 
named file::memory:?cache=shared.


-rw-r--r-- 1 wgo wgo 3072 Nov 5 04:00 file::memory:?cache=shared


I used the sqlite3 sources and also pre-build libraries to check if this might 
be a problem of an outdated version, but in both cases I get the same results.


To demonstrate what I'm doing I created a git repository containing a test 
program.


https://gitlab.com/laiki/sqlshared


You might need to adapt the CMakeLists.txt file to reflect your path to the 
sqlite lib.


Am I doing something wrong, or is this a known behavior?


Kind regards


Wasili
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

[Hick Gunter]

Foreign keys are ignored by default and need to be explicitly enabled. I would 
expect this to include everything that relates to foreign keys.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von a.furi...@lqt.it
Gesendet: Donnerstag, 08. November 2018 17:43
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] found a glitch in ALTER TABLE RENAME (3.25.x)

Hallo,

I've casually discovered that the behavior of ALTER TABLE RENAME TO (versione 
3.25.x) seems to be affected by an odd glitch; FOREIGN KEY constraints are 
updated as expected only when PRAGMA foreign_keys=1, otherwise they are just 
ignored.

example (common part)
-
CREATE TABLE mother (
 id INTEGER PRIMARY KEY,
 name TEXT);
CREATE TABLE daughter (
 id INTEGER PRIMARY KEY,
 id_mother INTEGER,
 name TEXT,
 CONSTRAINT fk_one FOREIGN KEY (id_mother)
 REFERENCES mother (id));

test #1
--
PRAGMA foreign_keys=0;
ALTER TABLE mother RENAME TO mom;
SELECT sql FROM sqlite_master WHERE name = 'daughter';  CREATE 
TABLE daughter (
 id INTEGER PRIMARY KEY,
 id_mother INTEGER,
 name TEXT,
 CONSTRAINT fk_one FOREIGN KEY (id_mother)
 REFERENCES mother (id))

as you can see, the FK constraint definition has not been updated.


test #2
--
PRAGMA foreign_keys=1;
ALTER TABLE mother RENAME TO mom;
SELECT sql FROM sqlite_master WHERE name = 'daughter';
--
CREATE TABLE daughter (
 id INTEGER PRIMARY KEY,
 id_mother INTEGER,
 name TEXT,
 CONSTRAINT fk_one FOREIGN KEY (id_mother)
 REFERENCES "mom" (id))

this second time the FK constraint has been properly updated.

Note: ALTER TABLE RENAME COLUMN seems to be immune from the issue.

test #3
--
PRAGMA foreign_keys=0;
ALTER TABLE mother RENAME COLUMN id TO pkuid; SELECT sql FROM sqlite_master 
WHERE name = 'daughter';  CREATE TABLE daughter (
 id INTEGER PRIMARY KEY,
 id_mother INTEGER,
 name TEXT,
 CONSTRAINT fk_one FOREIGN KEY (id_mother)
 REFERENCES mother (pkuid))

test #3
--
PRAGMA foreign_keys=1;
ALTER TABLE mother RENAME COLUMN id TO pkuid; SELECT sql FROM sqlite_master 
WHERE name = 'daughter';  CREATE TABLE daughter (
 id INTEGER PRIMARY KEY,
 id_mother INTEGER,
 name TEXT,
 CONSTRAINT fk_one FOREIGN KEY (id_mother)
 REFERENCES mother (pkuid))

the FK constraint definition is correctly updated in both cases, the actual 
setting of PRAGMA foreign_key is not relevant.

best regards,
Sandro Furieri (developer of SpatiaLite)


___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query regression with virtual tables

[Hick Gunter]

On upgrading from 3.7.14 to 3.24 (at last) I found a very detrimental change in 
the query implementation for a certain type of query:

SELECT <...> FROM  WHERE a IN () AND b IN () AND 
c =  AND timestamp between  AND  ORDER BY timestamp 
DESC LIMIT ,;

In 3.7.14 the xBestIndex function was called with 3 constraints 
{(c,'='),(timestamp, '<='),(timestamp,'>=)}. The function sets argvIndex values 
and OMIT flags for all three constraints, an index number and the 
orderByConsumed flag.

The created bytecode is

- create & populate ephemeral table a
- create & populate ephemeral table b
- loop over virtual table
-- check field a value IN ephemeral table a
-- check field b value IN ehpemeral table b
-next

In 3.24 the xBestIndex fuction is called with two additional constraints 
{(a,'='),(b.'=')}. The function sets argvIndex values and OMIT flags for all 
five constraints, an index number and the orderByConsumed flag.

The created bytecode is

- create & populate ephemeral table a
- create & populate ephemeral table b
- loop over ephemeral table a
-- loop over ephemeral table b
--- loop over virtual table
- sort result set
- return LIMIT rows


The virtual table is actually a partitioned table that will search only the 
partitions that cover the selected range of timestamps and implements merge 
logic. It will therefore accept and pass on addtional constraints to the 
subquery against the partition members. Unfortunately, the NGQP seems to be 
asking about a join with ephemeral tables, which precludes using CROSS JOIN to 
force a performant query plan that returns correctly ordered result rows.

Of course I could rewrite this as a sequence of statements approximately like:

BEGIN;
CREATE TEMP TABLE a_values AS VALUES()...
CREATE TEMP TABLE b_values AS VALUES()...
SELECT <...> FROM  CROSS JOIN a_values ON (a) CROSS JOIN 
b_values ON (b) WHERE c =  AND timestamp between  AND  
ORDER BY timestamp DESC LIMIT ,;
DROP TABLE a_values;
DROP TABLE b_values;
COMMIT;

But this results in two nested loops (even if an index is added on teach temp 
table). How can I get the ephemeral table lookups back?


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Query regression with virtual tables

[Hick Gunter]

In the meantime, I have found that restricting usable constraints to the fields 
present in the selected virtual index nearly restores the orginal query plan. 
Field c is now handled by SQlite at the outer level instead of in the query 
issued to member tables, forcing more records to be processed by the partition 
software.

The created bytecode is now

- create & populate ephemeral table a
- create & populate ephemeral table b
- loop over virtual table
-- check field a value IN ephemeral table a
-- check field b value IN ehpemeral table b
-- check field c value
 -next

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Hick Gunter
Gesendet: Montag, 12. November 2018 11:52
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] [sqlite] Query regression with virtual tables

On upgrading from 3.7.14 to 3.24 (at last) I found a very detrimental change in 
the query implementation for a certain type of query:

SELECT <...> FROM  WHERE a IN () AND b IN () AND 
c =  AND timestamp between  AND  ORDER BY timestamp 
DESC LIMIT ,;

In 3.7.14 the xBestIndex function was called with 3 constraints 
{(c,'='),(timestamp, '<='),(timestamp,'>=)}. The function sets argvIndex values 
and OMIT flags for all three constraints, an index number and the 
orderByConsumed flag.

The created bytecode is

- create & populate ephemeral table a
- create & populate ephemeral table b
- loop over virtual table
-- check field a value IN ephemeral table a
-- check field b value IN ehpemeral table b
 -next

In 3.24 the xBestIndex fuction is called with two additional constraints 
{(a,'='),(b.'=')}. The function sets argvIndex values and OMIT flags for all 
five constraints, an index number and the orderByConsumed flag.

The created bytecode is

- create & populate ephemeral table a
- create & populate ephemeral table b
- loop over ephemeral table a
-- loop over ephemeral table b
--- loop over virtual table
- sort result set
- return LIMIT rows


The virtual table is actually a partitioned table that will search only the 
partitions that cover the selected range of timestamps and implements merge 
logic. It will therefore accept and pass on addtional constraints to the 
subquery against the partition members. Unfortunately, the NGQP seems to be 
asking about a join with ephemeral tables, which precludes using CROSS JOIN to 
force a performant query plan that returns correctly ordered result rows.

Of course I could rewrite this as a sequence of statements approximately like:

BEGIN;
CREATE TEMP TABLE a_values AS VALUES()...
CREATE TEMP TABLE b_values AS VALUES()...
SELECT <...> FROM  CROSS JOIN a_values ON (a) CROSS JOIN 
b_values ON (b) WHERE c =  AND timestamp between  AND  
ORDER BY timestamp DESC LIMIT ,; DROP TABLE a_values; DROP 
TABLE b_values; COMMIT;

But this results in two nested loops (even if an index is added on teach temp 
table). How can I get the ephemeral table lookups back?


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLITE_OPEN_FULLMUTEX

[Hick Gunter]

Using SQLITE_OPEN_FULLMUTEX puts SQLite into serialized mode. This means that 
mutltiple threads can share a single connection but will block until the mutex 
is freed because the thread currently holding the mutex has left the SQLite 
code. Blocked threads will only experience a time delay (concurrency is 
reduced).

This is distinct from the SQLITE_LOCKED, which means that there is a (logical) 
conflict iin the same DB connection, e.g. attempting to drop a table in one 
thread while reading from the same table in another thread.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Mittwoch, 28. November 2018 08:33
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] SQLITE_OPEN_FULLMUTEX

Hi Members,

I enabled the SQLITE_OPEN_FULLMUTEX in that case if one thread is trying to 
write in to the DB and other thread is trying to read from the DB (Same 
connection). In that case will it cause any SQLITE_LOCKED error. In some of the 
forum i found that if we enable the SQLITE_OPEN_FULLMUTEX  the  sqlite handle ( 
sqlite3 * handle) contains the mutex filed so when the sqlite library is 
invoking that API will wait for the mutex to get open.



Thank you
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Bug? Confused data entry with column name

[Hick Gunter]

Works as advertised. "Description" (with double quotes) is a field name. 
'Description' with single quotes is a string constant.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dale Mellor
Gesendet: Mittwoch, 28. November 2018 06:42
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Bug? Confused data entry with column name

  THIS VERSION
SQLite 3.25.3 2018-11-05 20:37:38
89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b750alt1
zlib version 1.2.8
gcc-8.2.0


>   THIS SCRIPT
create table test (id varchar, description varchar); insert into test (id, 
description) values ("Description", "Duh"); insert into test (id, description) 
values ("Daft", "Daft"); select rowid, * from test where id="Description";



>>   PRODUCES
2|Daft|Daft



>>   BUT EXPECTED
1|Description|Duh


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Getting SQLITE_LOCKED

[Hick Gunter]

Locking (errors SQLITE_LOCKED and SQLITE_BUSY) are about **WHAT** you are doing 
to the DB. There can be at most 1 thread (same process or different process, it 
does not matter) with a write transaction on any DB file at any time. 
Typically, a transaction involves multiple calls to sqlite functions 
(sqlite3_prepare() to compile the statement, the first call to sqlite3_step() 
starts the transaction and calling sqlite3_reset() or sqlite3_finalize() ends 
the transaction).

FULLMUTEX has nothing to do with transactions. Using threads is about **HOW** 
you are doing things to the DB. FULLMUTEX is about making sure that your 
threads take turns when sharing a single connection (with a single transaction).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Donnerstag, 29. November 2018 12:37
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Getting SQLITE_LOCKED

Hi Team,

I am writing to a table 1 and reading from table 2 both operation are from 
different DB connection i am getting SQLITE_LOCKED

and when i try to read and write the same table from different connection i am 
getting same error. (I know read and write are the incompatible at same time). 
Why this happening bu default sqlite will act on FULLMUTEXT.

Thank You
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Strange query result

[Hick Gunter]

Works as expected. Maybe you are not declaring the correct types and end up 
comparing text to integer (1 is not equal to '1', unless an implicit or 
explicit cast is involved).

asql> create temp table flags( flag integer );
asql> insert into flags values (0),(1),(2),(3),(4),(5),(6),(7);
rows inserted
-
8

asql> select * from flags where flag & 1;
flag
--
1
3
5
7
asql> select * from flags where flag & 1 == 1;
flag
--
1
3
5
7
asql> select * from flags where flag & 1 != 0;
flag
--
1
3
5
7
asql> select * from flags where (flag & 1) != 0;
flag
--
1
3
5
7
asql> select * from flags where (flag & 1) != 0;
flag
--
1
3
5
7

asql> select * from flags where flag & (1|2);
flag
--
1
2
3
5
6
7
asql> select * from flags where flag & (1|2) != 0;
flag
--
1
2
3
5
6
7
asql> select * from flags where (flag & (1|2)) != 0;
flag
--
1
2
3
5
6
7

asql> select * from flags where flag & (1|2) == (1|2);
flag
--
3
7asql> select * from flags where (flag & (1|2)) == (1|2);
flag
--
3
7

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Maurice van der Stee
Gesendet: Donnerstag, 29. November 2018 13:53
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Strange query result

Hello,

I have a sqlite database containing a table config with amongst others the 
column conf_flags. I want to select entries from this table which have one or 
more specific flags set.

If do

select config_flags from config where config_flags & WANTED_FLAG != 0.

I get the correct result. Now this works if there is only one flag to test for. 
With multiple flags this works:

select config_flags from config where (config_flags & (WANTED_FLAG1 |
WANTED_FLAG2))  != 0

but this also returns the rows with only one of the flags set, which is not 
what I want.

This returns 0 rows:

select config_flags from config where (config_flags &
WANTED_FLAG)  =  WANTED_FLAG.

The strange thing is that if I do

select config_flags, config_flags & WANTED_FLAG  from config where 
(config_flags & WANTED_FLAG) != 0.

This has the correct evaluation in the second result column.

So what am I doing wrong.

===
Maurice van der Stee (s...@planet.nl)
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Strange query results.

[Hick Gunter]

Just as expected. You are comparing INTEGER to TEXT values. '4' is a text 
value, not an integer.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Maurice van der Stee
Gesendet: Donnerstag, 29. November 2018 14:46
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Strange query results.

This reproduces the issue for me:

create table config (config_package integer, config_flags integer); insert into 
config (config_package, config_flags) values (1, 2); insert into config 
(config_package, config_flags) values (2, 4); insert into config 
(config_package, config_flags) values (3, 6); select config_package, 
config_flags, (config_flags & '4') from config where (config_flags & '4') != 
'4';

This produces:

1|2|0
2|4|4
3|6|4

While it should only have returned the first row.
--
===
Maurice van der Stee (s...@planet.nl)
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] sqlite3_exec()

[Hick Gunter]

An SQLite connection is not a unix standard file handle. By using only one 
connection for two concurrent tasks, you are getting interference from 
operations which would usually be isolated from each other. Sharing a 
connection between threads is there because SQlite also runs on embedded 
systems that may have low limits on the number of file handles a process may 
open simultaneously.

Using 1 connection per thread will allow the reader thread to read all of the 
"old" records (and none of the "new" records). Then, the writer can add the 
"new" records. A subsequent read will return both "old " and "new" records.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Samstag, 01. Dezember 2018 14:51
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] sqlite3_exec()

Hi Team,
Is this sqlite3_exec() function is a blocking call in the case of writing.I 
have two thread one is for reading and other is for writing the DB. Both thread 
have the same DB connection(Of course i am in FULLMUTEX mode and sqlite point 
of few there is no multiple thread every thing is serialized). Reader thread 
starts first and continuously reading 500 records from a table (I kept the 
sqlite3_exec() function call inside a loop for continues reading ie, on one 
iteration it will print 500 records and it is printing successfully using 
callback function). After 1sec of delay writer thread starts its execution and 
tries to write 500 records into the same table,Because of the small loop 
iteration gap in the reader thread the writer will get a chance to update the 
table hence my reader thread is blocked may be the mutex on the sqlite3* is 
acquired by the writer thread. After around 45 sec reader start its operation 
but one thing i observed is that the newly added entry is not printing
  instead of that old data is printing so i increased the reader thread looping 
iteration. After few iteration is over the newly added records also printing 
along with the old data. Why this delay is happening? (even though the writer 
thread is not coming out from the sqlite3_exec which i used to write the data 
but after a few sec its came out at that time onward i am getting the updated 
data). Along with the INSERT sql statement i am not using any BEGIN TRANSACTION 
and COMMIT.
Please give a suggestion for this.

Thank you
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] geopoly_contains_point(P, X, Y) doc is overly modest

[Hick Gunter]

Maybe you should be using IS TRUE. See https://sqlite.org/lang_expr.html

 Boolean Expressions

 The SQL language features several contexts where an expression is 
evaluated and the result converted to a boolean (true or false) value. These 
contexts are:
 • the WHERE clause of a SELECT, UPDATE or DELETE statement,
 • the ON or USING clause of a join in a SELECT statement,
 • the HAVING clause of a SELECT statement,
 • the WHEN clause of an SQL trigger, and
 • the WHEN clause or clauses of some CASE expressions.

 To convert the results of an SQL expression to a boolean value, SQLite 
first casts the result to a NUMERIC value in the same way as a CAST expression. 
A numeric zero value (integer value 0 or real value 0.0) is considered to be 
false. A NULL value is still NULL. All other values are considered true.

 For example, the values NULL, 0.0, 0, 'english' and '0' are all considered 
to be false. Values 1, 1.0, 0.1, -0.1 and '1english' are considered to be true.

 Beginning with SQLite 3.23.0 (2018-04-02), SQLite recognizes the 
identifiers "TRUE" and "FALSE" as boolean literals, if and only if those 
identifiers are not already used for some other meaning. If there already 
exists columns or tables or other objects named TRUE or FALSE, then for the 
sake of backwards compatibility, the TRUE and FALSE identifiers refer to those 
other objects, not to the boolean values.

 The boolean identifiers TRUE and FALSE are usually just aliases for the 
integer values 1 and 0, respectively. However, if TRUE or FALSE occur on the 
right-hand side of an IS operator, then they form new unary postfix operators 
"IS TRUE" and "IS FALSE" which test the boolean value of the operand on the 
left.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Larry Brasfield
Gesendet: Samstag, 01. Dezember 2018 19:39
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] geopoly_contains_point(P,X,Y) doc is overly modest

The documentation at https://www.sqlite.org/geopoly.html , at 3.8. for 
geopoly_contains_point(), asserts that the function “returns true if and only 
if the coordinate X,Y is inside or on the boundary of the polygon P.”  As now 
implemented, in the v3.26 release, it returns 1 where the point is on a 
boundary and 2 where the point is inside of the boundary.  While the 
documentation is technically correct, (for a suitable definition of “true” 
differing from its meaning in SQL), this seems to be a useful behavior worthy 
of exposure.

I suggest the rewording, “returns 2 if the coordinate X,Y is inside polygon P, 
1 if on the boundary, or 0 otherwise.”  I submit that this might help avoid 
errors such as “… WHERE geopoly_contains_point(_shape, ptX, ptY) = TRUE”, which 
will produce surprise for those who read the present claim literally.
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.

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

Re: [sqlite] [EXTERNAL] sqlite3_exec()

[Hick Gunter]

This is exactly the expected behaviour of „journal mode“ with an insufficient 
timeout value in the reader connection (or none set at all). This is 
technically not an error condition, just a notification that the requested 
operation cannot be done „just right now“ and needs to be retried „later“.

Set or increase the timeout value on both connections to resolve the issue.

Alternatively, switch into „WAL mode“. This will make the writer save it‘s 
changes in a Write Ahead Logfile without interfering with readers, who will 
still see the state of the DB at the time their transaction started.

Von: Prajeesh Prakash [mailto:prajeeshprakash@elear.solutions]
Gesendet: Montag, 03. Dezember 2018 08:39
An: SQLite mailing list ; Hick Gunter 

Betreff: Re: [sqlite] [EXTERNAL] sqlite3_exec()


Thank you for the response.

Using 1 connection per thread will allow the reader thread to read all of the 
"old" records (and none of the "new" records). Then, the writer can add the 
"new" records. A subsequent read will return both "old " and "new" records.

If this is the case the reader thread on connection 1 will get a SQLITE_BUSY if 
the writer thread is writing on DB using connection 2. Because when i tested 
the scenario (its Doesn't matter which table i am writing/reading) like writing 
and reading using 2 different connection i  am getting BUSY error status 
(Writing and reading same table, Writing and reading two different table) .

On December 3, 2018 at 12:56 PM Hick Gunter 
mailto:h...@scigames.at>> wrote:

An SQLite connection is not a unix standard file handle. By using only one 
connection for two concurrent tasks, you are getting interference from 
operations which would usually be isolated from each other. Sharing a 
connection between threads is there because SQlite also runs on embedded 
systems that may have low limits on the number of file handles a process may 
open simultaneously.

Using 1 connection per thread will allow the reader thread to read all of the 
"old" records (and none of the "new" records). Then, the writer can add the 
"new" records. A subsequent read will return both "old " and "new" records.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Samstag, 01. Dezember 2018 14:51
An: SQLite mailing list 
mailto:sqlite-users@mailinglists.sqlite.org>>
Betreff: [EXTERNAL] [sqlite] sqlite3_exec()

Hi Team,
Is this sqlite3_exec() function is a blocking call in the case of writing.I 
have two thread one is for reading and other is for writing the DB. Both thread 
have the same DB connection(Of course i am in FULLMUTEX mode and sqlite point 
of few there is no multiple thread every thing is serialized). Reader thread 
starts first and continuously reading 500 records from a table (I kept the 
sqlite3_exec() function call inside a loop for continues reading ie, on one 
iteration it will print 500 records and it is printing successfully using 
callback function). After 1sec of delay writer thread starts its execution and 
tries to write 500 records into the same table,Because of the small loop 
iteration gap in the reader thread the writer will get a chance to update the 
table hence my reader thread is blocked may be the mutex on the sqlite3* is 
acquired by the writer thread. After around 45 sec reader start its operation 
but one thing i observed is that the newly added entry is not printing
instead of that old data is printing so i increased the reader thread looping 
iteration. After few iteration is over the newly added records also printing 
along with the old data. Why this delay is happening? (even though the writer 
thread is not coming out from the sqlite3_exec which i used to write the data 
but after a few sec its came out at that time onward i am getting the updated 
data). Along with the INSERT sql statement i am not using any BEGIN TRANSACTION 
and COMMIT.
Please give a suggestion for this.

Thank you

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

___
Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.

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



___
Gunter Hick | Software Engineer | Scientific Games International 
GmbH<http://www.s

Re: [sqlite] [EXTERNAL] How to Save NULL data to DB

[Hick Gunter]

If you store NULL then SQLite will return NULL. Maybe your presentation layer 
is converting NULL to text.

The literal NULL is used in the query text, or you can call sqlite3_bind_null() 
for a parameter or sqlite3_result_null() to set the result of a user written 
function.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Mittwoch, 05. Dezember 2018 09:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] How to Save NULL data to DB

Hi Team,

How to save NULL parameter into the DB. I saw something like (null) on table 
but when i am reading from the table it popup as a string not the NULL value.
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Number of changes

[Hick Gunter]

RTM.

Sqlite3_total_changes() counts the *total* number of changes done since the 
connection was opened. To get the number of changes made by the *last 
statement*, use sqlite3_changes(). This applies only to connections that are 
not shared between threads.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Mittwoch, 05. Dezember 2018 13:10
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Number of changes

Hi team,

How we can find the number of changes done on a particular table in DB using 
sqlite3 library function (C program). I tried with sqlite3_total_changes() but 
its doesn't helps me. I am writing only one record but its giving me 22 
records. At present my db contains 23 table and i tried to INSERT record into 1 
table out of 23 after INSERT (i used sqlite3_exec()) i kept 
sqlite3_total_changes() but it giving result as 22.



Thank you
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WG: [EXTERNAL] Number of changes

[Hick Gunter]

Von: Prajeesh Prakash [mailto:prajeeshprakash@elear.solutions]
Gesendet: Mittwoch, 05. Dezember 2018 13:47
An: SQLite mailing list ; Hick Gunter 

Betreff: Re: [sqlite] [EXTERNAL] Number of changes


Thank you  i tried sqlite3_changes() but in that case i INSERT 2 rows but it 
gave 1.

On December 5, 2018 at 5:51 PM Hick Gunter 
mailto:h...@scigames.at>> wrote:

RTM.

Sqlite3_total_changes() counts the *total* number of changes done since the 
connection was opened. To get the number of changes made by the *last 
statement*, use sqlite3_changes(). This applies only to connections that are 
not shared between threads.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Mittwoch, 05. Dezember 2018 13:10
An: 
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [sqlite] Number of changes

Hi team,

How we can find the number of changes done on a particular table in DB using 
sqlite3 library function (C program). I tried with sqlite3_total_changes() but 
its doesn't helps me. I am writing only one record but its giving me 22 
records. At present my db contains 23 table and i tried to INSERT record into 1 
table out of 23 after INSERT (i used sqlite3_exec()) i kept 
sqlite3_total_changes() but it giving result as 22.

Thank you

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

___
Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.

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



___
Gunter Hick | Software Engineer | Scientific Games International 
GmbH<http://www.scigames.at> | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, 
HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WG: [EXTERNAL] Number of changes

[Hick Gunter]

Show what you executed. Make sure that there is no interference from other 
threads on the same connection. If you execute more than 1 statement in one 
call to sqlite3_exec(), only the last INSERT/UPDATE/DELETE will determine the 
return value of sqlite3_changes(). If you want the aggregate count of changes 
from 1 call to sqlite3_exec(), take the difference of the 
sqlite3_total_changes() values before and after.

Von: Prajeesh Prakash [mailto:prajeeshprakash@elear.solutions]
Gesendet: Mittwoch, 05. Dezember 2018 13:47
An: SQLite mailing list 
mailto:sqlite-users@mailinglists.sqlite.org>>;
 Hick Gunter mailto:h...@scigames.at>>
Betreff: Re: [sqlite] [EXTERNAL] Number of changes


Thank you  i tried sqlite3_changes() but in that case i INSERT 2 rows but it 
gave 1.

On December 5, 2018 at 5:51 PM Hick Gunter 
mailto:h...@scigames.at>> wrote:

RTM.

Sqlite3_total_changes() counts the *total* number of changes done since the 
connection was opened. To get the number of changes made by the *last 
statement*, use sqlite3_changes(). This applies only to connections that are 
not shared between threads.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Mittwoch, 05. Dezember 2018 13:10
An: 
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [sqlite] Number of changes

Hi team,

How we can find the number of changes done on a particular table in DB using 
sqlite3 library function (C program). I tried with sqlite3_total_changes() but 
its doesn't helps me. I am writing only one record but its giving me 22 
records. At present my db contains 23 table and i tried to INSERT record into 1 
table out of 23 after INSERT (i used sqlite3_exec()) i kept 
sqlite3_total_changes() but it giving result as 22.

Thank you

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

___
Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.

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



___
Gunter Hick | Software Engineer | Scientific Games International 
GmbH<http://www.scigames.at> | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, 
HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Number of changes

[Hick Gunter]

Just as I said. You are executing *TWO* SEPARATE *INSERTS* OF *1 CHANGE* EACH. 
So sqlite3_changes() reports 1 change (from the second INSERT), just as it is 
supposed to.

If you call sqlite3_total_changes() before executing the inserts and again 
afterwards, the *difference* of the returned values will be 2.

Von: Prajeesh Prakash [mailto:prajeeshprakash@elear.solutions]
Gesendet: Mittwoch, 05. Dezember 2018 14:04
An: Hick Gunter 
Betreff: Re: AW: [sqlite] [EXTERNAL] Number of changes


I ma trying to two insert on a single sqlite3_exec()

eg:   char *url = ""BEGIN TRANSACTION;"INSERT INTO Cars(NAME) 
VALUES('ABC');"INSERT INTO Cars(NAME) VALUES('ABC');"COMMIT";
then i am calling sqlite3_exec() then sqlite3_change()
On December 5, 2018 at 6:23 PM Hick Gunter 
mailto:h...@scigames.at>> wrote:



Show what you executed. Make sure that there is no interference from other 
threads on the same connection. If you execute more than 1 statement in one 
call to sqlite3_exec(), only the last INSERT/UPDATE/DELETE will determine the 
return value of sqlite3_changes(). If you want the aggregate count of changes 
from 1 call to sqlite3_exec(), take the difference of the 
sqlite3_total_changes() values before and after.



Von: Prajeesh Prakash [mailto:prajeeshprakash@elear.solutions]
Gesendet: Mittwoch, 05. Dezember 2018 13:47
An: SQLite mailing list 
mailto:sqlite-users@mailinglists.sqlite.org>>;
 Hick Gunter mailto:h...@scigames.at>>
Betreff: Re: [sqlite] [EXTERNAL] Number of changes



Thank you  i tried sqlite3_changes() but in that case i INSERT 2 rows but it 
gave 1.

On December 5, 2018 at 5:51 PM Hick Gunter 
mailto:h...@scigames.at>> wrote:

RTM.

Sqlite3_total_changes() counts the *total* number of changes done since the 
connection was opened. To get the number of changes made by the *last 
statement*, use sqlite3_changes(). This applies only to connections that are 
not shared between threads.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Mittwoch, 05. Dezember 2018 13:10
An: 
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [sqlite] Number of changes

Hi team,

How we can find the number of changes done on a particular table in DB using 
sqlite3 library function (C program). I tried with sqlite3_total_changes() but 
its doesn't helps me. I am writing only one record but its giving me 22 
records. At present my db contains 23 table and i tried to INSERT record into 1 
table out of 23 after INSERT (i used sqlite3_exec()) i kept 
sqlite3_total_changes() but it giving result as 22.

Thank you

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

___
Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.

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




___
Gunter Hick | Software Engineer | Scientific Games International 
GmbH<http://www.scigames.at> | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, 
HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.



___
Gunter Hick | Software Engineer | Scientific Games International 
GmbH<http://www.scigames.at> | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, 
HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?)

[Hick Gunter]

... which is what SQLite does internally if you provide a list of literal 
values inside the parentheses. In some cases, SQLite 3.24 has been observed to 
use such an ephemeral table as the outer table of a join; with detrimental 
effects on query performance and no CROSS JOIN syntax available to force a 
different query plan

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von James K. Lowden
Gesendet: Mittwoch, 05. Dezember 2018 19:04
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)

On Fri, 30 Nov 2018 23:25:48 +0900
Simon Walter  wrote:

> > SELECT id, data FROM val WHERE id IN ("1, 893, 121212");
...
> I have no idea yet if MySQL and/or PostgreSQL can handle this scenario
> and how they do it.

The important thing to understand about parameterized queries is that they are 
not a generalized macro system. Only data -- not metadata, not arbitrary 
strings -- can be parameterized.  That's why your IN list can't be 
parameterized (except as individual elements) and why can't say

SELECT id, data FROM ?
or
SELECT id, ? FROM val

as would occasionally be convenient.

Other than string-slinging, the only generalized standard solution for your 
parameterized IN list, where the number of elements is variable, is to first 
insert the list into a table, then use IN or EXISTS against it.

--jkl
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?)

[Hick Gunter]

Maybe someone can come up with a CTE that works for this...

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Donnerstag, 06. Dezember 2018 10:38
An: General Discussion of SQLite Database 
Betreff: Re: [sqlite] [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?)

On Thu, Dec 6, 2018 at 8:49 AM Hick Gunter  wrote:

> ... which is what SQLite does internally if you provide a list of
> literal values inside the parentheses.
>

Which is IMHO a pity that there's no API to bind such an ephemeral table and 
'bind" it.

Heck, given how the carray() eponymous vtable works, just have SQLite itself 
manage that array internally, tying its lifetime to the statement's lifetime, 
and provide ways to "pushback"
typed values in a safe manner, possibly with the existing bind APIs, would 
achieve the desired result with very little code I suspect. Oh well... --DD 
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?)

[Hick Gunter]

Original query:

SELECT ... FROM mytable WHERE field_a IN ()AND field_b IN 
() AND 

This is sometimes (when mytable is a virtual table that offers to handle 
"field_a =" and "field_b =" constraints internallly) resolved as
- create table eph_a
- create table eph_b
- full table scan eph_a
- full table scan eph_b
- scan table mytable index #n

Target query:

WITH (...) SELECT ... FROM mytable LEFT JOIN cte_a ON (mytable.a = cte_a.a) 
LEFT JOIN cte_b ON (mytable.b = cte_b.b) WHERE 

Which should resolve as
- create table eph_a
- create table eph_b
- scan table mytable index #n
- lookup mytable.a in eph_a
- lookup mytable.b in eph_b

The cost of a partial index scan is O(log n) to locate the first record and 
O(m) for retrieving m consecutive records.
The cost of a lookup in an ephemeral table is O(log n).
The cost of a full table scan for an ephemeral table is O(n)

The cost of the first query plan is therefore O(a * b * (m + log n)) or O(a *b 
*m) + O(a * b * log n)
The cost of the second query plan is only O(log n + m * (log a + log b)) or 
O((log a*b) * m) + O(log n)

This makes the second query plan much less costly for IN lists of 2 or more 
elements.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Donnerstag, 06. Dezember 2018 11:32
An: General Discussion of SQLite Database 
Betreff: Re: [sqlite] [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?)

On Thu, Dec 6, 2018 at 11:10 AM Hick Gunter  wrote:

> Maybe someone can come up with a CTE that works for this...
>

How so? I'm not following you. Ryan already provided a CTE to transform a 
string into values, but that involves string parsing, and is kinda ugly (no 
offence Ryan :) ), and unlikely to be that efficient relative to carray. The 
point is that binding is *scalar only* in SQLite.

If OTOH, we could write:

...prepare...(db, "select ... from t where c in (?)", ..., &stmt, ...); 
...bind_array(stmt, 1, SQLITE_INTEGER, vec.size()); for (auto elem : vec) { 
...bind_int(stmt, 1, elem); }

...bind_array would instantiate the same array the carray() extension supports.
Existing typed bind APIs would fill in that array, with usual SQLite 
conversions, error checking against declared array-type.
And ...prepare would transparently transform WHERE c IN (?) with WHERE c IN
(carray(?))
with the internally managed C array.

Of course, at prepare time, SQLite doesn't know yet ? is not a scalar as usual, 
so perhaps a different notation is necessary, like the TABLE() operator in 
Oracle SQL for example.
But given carray() and the existing infrastructure, I naively fail to see how 
the above wouldn't work.

My $0.02. --DD

[1] https://www.sqlite.org/c3ref/c_blob.html
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Transaction commits hangs up

[Hick Gunter]

You are leaving out a lot of necessary detail. Are you using threads? Does each 
thread have it's own connection or are you sharing connections? Did you set a 
busy timeout?

BTW: Your Version of SQLiteis quite old.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Aydin Ozgur Yagmur
Gesendet: Dienstag, 11. Dezember 2018 09:53
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Transaction commits hangs up

I have been using sqlite3.8.11 on fat32 file system. Sometimes, while 
committing a transaction commit process hangs up and database became locked. I 
am trying to find a cause or a solution for this problem. Does anyone encounter 
with similar problem before? Do you have any suggestion or any idea for this 
problem?

Thank you,
yagmur
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Transaction commits hangs up

[Hick Gunter]

If you have only one thread accessing the database, how do you determine that 
it is hanging and that the database is locked?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Aydin Ozgur Yagmur
Gesendet: Dienstag, 11. Dezember 2018 11:31
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up

I set busy-timeout 1000 ms. I have been using threads but only one thread can 
access to database.
I will upgrade sqlite version as soon as possible. Could this lead to that 
problem?

On Tue, Dec 11, 2018 at 12:11 PM Hick Gunter  wrote:

> You are leaving out a lot of necessary detail. Are you using threads?
> Does each thread have it's own connection or are you sharing
> connections? Did you set a busy timeout?
>
> BTW: Your Version of SQLiteis quite old.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Aydin Ozgur Yagmur
> Gesendet: Dienstag, 11. Dezember 2018 09:53
> An: SQLite mailing list 
> Betreff: [EXTERNAL] [sqlite] Transaction commits hangs up
>
> I have been using sqlite3.8.11 on fat32 file system. Sometimes, while
> committing a transaction commit process hangs up and database became
> locked. I am trying to find a cause or a solution for this problem.
> Does anyone encounter with similar problem before? Do you have any
> suggestion or any idea for this problem?
>
> Thank you,
> yagmur
> ___
> 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
> | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013
> | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Transaction commits hangs up

[Hick Gunter]

That answers question 2. What about the thread? Can you check what the thread 
is doing with a debugger? Ist hat always the same location or is it maybe 
running in a loop? Is it always the same statement that "hangs"?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Aydin Ozgur Yagmur
Gesendet: Dienstag, 11. Dezember 2018 13:43
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up

I am trying to edit the database using command shell.
for instance i try to execute analyze commad i get "database is locked"
message

On Tue, Dec 11, 2018 at 1:36 PM Hick Gunter  wrote:

> If you have only one thread accessing the database, how do you
> determine that it is hanging and that the database is locked?
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Aydin Ozgur Yagmur
> Gesendet: Dienstag, 11. Dezember 2018 11:31
> An: SQLite mailing list 
> Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up
>
> I set busy-timeout 1000 ms. I have been using threads but only one
> thread can access to database.
> I will upgrade sqlite version as soon as possible. Could this lead to
> that problem?
>
> On Tue, Dec 11, 2018 at 12:11 PM Hick Gunter  wrote:
>
> > You are leaving out a lot of necessary detail. Are you using threads?
> > Does each thread have it's own connection or are you sharing
> > connections? Did you set a busy timeout?
> >
> > BTW: Your Version of SQLiteis quite old.
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users
> > [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > Im Auftrag von Aydin Ozgur Yagmur
> > Gesendet: Dienstag, 11. Dezember 2018 09:53
> > An: SQLite mailing list 
> > Betreff: [EXTERNAL] [sqlite] Transaction commits hangs up
> >
> > I have been using sqlite3.8.11 on fat32 file system. Sometimes,
> > while committing a transaction commit process hangs up and database
> > became locked. I am trying to find a cause or a solution for this problem.
> > Does anyone encounter with similar problem before? Do you have any
> > suggestion or any idea for this problem?
> >
> > Thank you,
> > yagmur
> > ___
> > 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
> > | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
> > | 0430013
> > | (O)
> > +43 1 80100 - 0
> >
> > May be privileged. May be confidential. Please delete if not the
> addressee.
> > ___
> > 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
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH
> | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013
> | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Transaction commits hangs up

[Hick Gunter]

Obviously you are using some kind of wrapper and a currently undisclosed 
programming language running under some OS you have neglected to name to call 
SQLite.

Without the aid of a debugger, it will not even be possible to discern if the 
problem is in SQLite itself or the wrapper code you are using.

Can you provide a simple schema and a query that exhibits the problem? Can you 
reproduce the problem by running the statements from the SQLite shell?

In 5 posts so far, you have volunteered practically no information at all, 
which severely limits the amount of help you can be provided with.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Aydin Ozgur Yagmur
Gesendet: Dienstag, 11. Dezember 2018 14:20
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up

*Ist hat always the same location or is it maybe running in a loop? Is it 
always the same statement that "hangs"? *
>>>
I use sqlite for more than 3 years, i encouter such an error for the first 
time. That is, no.
*Can you check what the thread is doing with a debugger? *
>>>
I have been logging the situation.
log--> before commit
  sqlitetx->commit();
log--> after commit
i can see "before commit" log but cannot see "after commit" log.



On Tue, Dec 11, 2018 at 3:48 PM Hick Gunter  wrote:

> That answers question 2. What about the thread? Can you check what the
> thread is doing with a debugger? Ist hat always the same location or
> is it maybe running in a loop? Is it always the same statement that "hangs"?
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Aydin Ozgur Yagmur
> Gesendet: Dienstag, 11. Dezember 2018 13:43
> An: SQLite mailing list 
> Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up
>
> I am trying to edit the database using command shell.
> for instance i try to execute analyze commad i get "database is locked"
> message
>
> On Tue, Dec 11, 2018 at 1:36 PM Hick Gunter  wrote:
>
> > If you have only one thread accessing the database, how do you
> > determine that it is hanging and that the database is locked?
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users
> > [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > Im Auftrag von Aydin Ozgur Yagmur
> > Gesendet: Dienstag, 11. Dezember 2018 11:31
> > An: SQLite mailing list 
> > Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up
> >
> > I set busy-timeout 1000 ms. I have been using threads but only one
> > thread can access to database.
> > I will upgrade sqlite version as soon as possible. Could this lead
> > to that problem?
> >
> > On Tue, Dec 11, 2018 at 12:11 PM Hick Gunter  wrote:
> >
> > > You are leaving out a lot of necessary detail. Are you using threads?
> > > Does each thread have it's own connection or are you sharing
> > > connections? Did you set a busy timeout?
> > >
> > > BTW: Your Version of SQLiteis quite old.
> > >
> > > -Ursprüngliche Nachricht-
> > > Von: sqlite-users
> > > [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > > Im Auftrag von Aydin Ozgur Yagmur
> > > Gesendet: Dienstag, 11. Dezember 2018 09:53
> > > An: SQLite mailing list 
> > > Betreff: [EXTERNAL] [sqlite] Transaction commits hangs up
> > >
> > > I have been using sqlite3.8.11 on fat32 file system. Sometimes,
> > > while committing a transaction commit process hangs up and
> > > database became locked. I am trying to find a cause or a solution
> > > for this
> problem.
> > > Does anyone encounter with similar problem before? Do you have any
> > > suggestion or any idea for this problem?
> > >
> > > Thank you,
> > > yagmur
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-use
> > > rs
> > >
> > >
> > > ___
> > >  Gunter Hick | Software Engineer | Scientific Games International
> > > GmbH
> > > | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
> > > | 0430013
> > > | (O)
> > > +43 1 80100 - 0
> > >
> > > May be privileged. May be confidential. Please delete if not the
> > addressee.
> > > ___
> > > sqlite-use

Re: [sqlite] [EXTERNAL] Transaction commits hangs up

[Hick Gunter]

Diagnosis is quite straightforward: If you can reproduce the problem by running 
your statements in the SQLite shell, then there is a problem in SQLite. If not, 
then it is caused by the wrapper.

The wrapper having a commit method is a strong indication that it has an 
internal model of SQLite transactions and you cannot tell when it is calling 
which of the SQLite interface functions.

As you are running Linux, you should have a debugger available (gdb perhaps) so 
you can connect to your process in the hung state and dump a backtrace. If you 
can compile your own SQLite image for debugging, you may even be able to tell 
which source code line the process is hanging at.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Aydin Ozgur Yagmur
Gesendet: Dienstag, 11. Dezember 2018 15:14
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up

I have already been upgrading sqlite version. After this If I encounter with 
the same problem I will inform you..
For Hick Gunter
I'm sorry for the inconveinence.
I have been using C++.
I have been using some kind of wrapper : yes.
Operating system (cat /proc/version): Linux version 
3.17.0-xilinx-00016-g9d68a4a-dirty
I cannot reproduce the problem as I already said I have encountered such an 
error for the first time. But i'm sure that the wrapper cannot cause such a 
problem.

For Simon Slavin
Ok, if i encounter with same problem, I will use command shell, enter pragma 
command and after that I will execute analyze command.


If you have any idea which may cause to this problem please let me know.
Thank you very much for your help.
Best regards




On Tue, Dec 11, 2018 at 4:39 PM Hick Gunter  wrote:

> Obviously you are using some kind of wrapper and a currently
> undisclosed programming language running under some OS you have
> neglected to name to call SQLite.
>
> Without the aid of a debugger, it will not even be possible to discern
> if the problem is in SQLite itself or the wrapper code you are using.
>
> Can you provide a simple schema and a query that exhibits the problem?
> Can you reproduce the problem by running the statements from the SQLite shell?
>
> In 5 posts so far, you have volunteered practically no information at
> all, which severely limits the amount of help you can be provided with.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Aydin Ozgur Yagmur
> Gesendet: Dienstag, 11. Dezember 2018 14:20
> An: SQLite mailing list 
> Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up
>
> *Ist hat always the same location or is it maybe running in a loop? Is
> it always the same statement that "hangs"? *
> >>>
> I use sqlite for more than 3 years, i encouter such an error for the
> first time. That is, no.
> *Can you check what the thread is doing with a debugger? *
> >>>
> I have been logging the situation.
> log--> before commit
>   sqlitetx->commit();
> log--> after commit
> i can see "before commit" log but cannot see "after commit" log.
>
>
>
> On Tue, Dec 11, 2018 at 3:48 PM Hick Gunter  wrote:
>
> > That answers question 2. What about the thread? Can you check what
> > the thread is doing with a debugger? Ist hat always the same
> > location or is it maybe running in a loop? Is it always the same
> > statement that
> "hangs"?
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users
> > [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > Im Auftrag von Aydin Ozgur Yagmur
> > Gesendet: Dienstag, 11. Dezember 2018 13:43
> > An: SQLite mailing list 
> > Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up
> >
> > I am trying to edit the database using command shell.
> > for instance i try to execute analyze commad i get "database is locked"
> > message
> >
> > On Tue, Dec 11, 2018 at 1:36 PM Hick Gunter  wrote:
> >
> > > If you have only one thread accessing the database, how do you
> > > determine that it is hanging and that the database is locked?
> > >
> > > -Ursprüngliche Nachricht-
> > > Von: sqlite-users
> > > [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > > Im Auftrag von Aydin Ozgur Yagmur
> > > Gesendet: Dienstag, 11. Dezember 2018 11:31
> > > An: SQLite mailing list 
> > > Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up
> > >
> > > I set busy-timeout 1000 ms. I have been using threads but only one
> > > thread can access to database.
> > > I will upgrade

Re: [sqlite] [EXTERNAL] Re: SQLITE gives incorrect results for 'NOT IN' query if partial index exists

[Hick Gunter]

Serves you right for spying on your boyfriend ;P

Check the link at the bottom of each and every message from the list for the 
way to unsubscribe. We don't enjoy the prospect of free floating pieces of 
brain on this list ;)

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nicole Sexton
Gesendet: Mittwoch, 12. Dezember 2018 11:09
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' 
query if partial index exists

I'm very confused as I never sent that e-mail and no longer want to receive 
these e-mails. I signed up a long time ago to try to figure out what my 
boyfriend was up to and decrypt his messages. I somehow stubbled upon here 
joined the mailing list like an idiot. Like I have 0 clue hoe any of this 
works. I'd be forever thankful to not get these e-mails and longer. Someone 
must be using my e-mail. ugh. Any suggestions?? I mean I would love to learn 
all this but my brain may explode.


> On Dec 11, 2018, at 2:17 PM, Luuk  wrote:
>
>
> On 11-12-2018 10:09, Wout Mertens wrote:
>> Hi Luuk,
>>
>> Not sure if you realize this, but your email comes over as very
>> aggressive, and if there's one person on this mailing list that
>> doesn't deserve that, it's dr Hipp.
>>
>> In particular, the quotes around forgot seem to imply that it was
>> forgotten on purpose.
>>
>> Personally, I would have worded it as "I looked at the test and I
>> wonder if this test case is addressed". Email communication is easy
>> to misconstrue…
>>
>> Cheers,
>>
>> Wout.
>>
>
> Sorry, again sorry,
>
> it must have been an interlingual misphrased wording of /me  ;)
>
> ___
> 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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Question about floating point

[Hick Gunter]

That is why integer money amounts have a value/scale pair instead of 
mantissa/exponent. I don't expect you would be given change in the dimension of 
1 "zimba" if you need 100 trillion to pay for a tank of gas.

The principle behind  money = v * 10 ^ -s is just the same as float = m * 2 ^e, 
but avoids the rounding inherent in transforming from base 2 to base 10.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Wout Mertens
Gesendet: Sonntag, 16. Dezember 2018 15:55
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Question about floating point

On Sat, Dec 15, 2018 at 7:13 PM Keith Medcalf  wrote:

>
> >And yet ... here we are.  The post which started this thread summed
> >currency amounts and reached a total of 211496.252 .
>
> >Yes, you can say 'that would have been rounded before it was
> >printed'.  But then you're into the old questions: do you round at
> >every step, or only at the end ?  Do you round or truncate ?  Where
> >does the fraction go ?  etc. etc..
>
> You apply half-even rounding (not elementary school 4/5 rounding) only
> for display (output) and never round intermediates.  The "fraction"
> does not exist ... Though if you do 4/5 rounding rather than half-even
> rounding the accumulated errors will amount to quite a sum.
>

TIL, thanks!

I'd also like to point out a problem with integer money: inflation. For USD 
it's been OK so far, but imagine having to handle the Zimbabwean Dollar, which 
ended up having 100 trillion dollar notes. Good way to overflow your integers.

With floating point, that's not a problem.
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Sample Employee database ported to SQLite from MySQL

[Hick Gunter]

Perhaps it is designed to be "somewhat nonstandard" in order to enable edge 
cases in testing.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Chris Locke
Gesendet: Mittwoch, 19. Dezember 2018 11:55
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Sample Employee database ported to SQLite from 
MySQL

The scheme (for me) is like nails on a chalkboard.  'dept_no' but defined as a 
'CHAR', then 'emp_no' as an INT.
Fields with '_no' are read as 'number' and so should be a number.  OK, that 
doesn't always work for 'telephone_no' (they usually start with a 0 ...
well, they do in the UK where I am...)
But I digress..


Chris


On Wed, Dec 19, 2018 at 9:05 AM Arun - Siara Logics (cc) 
wrote:

> This project (https://github.com/siara-cc/employee_db) hosts the
> Sqlite3 db file ported from mysql test_db found at
> https://github.com/datacharmer/test_db. It can be used to test your
> applications and database servers. To use this project, download
> employees.db.bz2, unzip and open using sqlite3 command line tool.
>
> The original data was created by Fusheng Wang and Carlo Zaniolo at
> Siemens Corporate Research. The data is in XML format.
> http://timecenter.cs.aau.dk/software.htm
>
> Giuseppe Maxia made the relational schema and Patrick Crews exported
> the data in relational format.
>
> The database contains about 300,000 employee records with 2.8 million
> salary entries. The export data is 167 MB, which is not huge, but
> heavy enough to be non-trivial for testing.
>
> A picture of the schema can be found at:
> https://github.com/siara-cc/employee_db/blob/master/employees-schema.p
> ng?raw=true
>
> Regards
> Arun - Siara Logics (cc)
>
>
> ___
> 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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Banking calender (was: Question about floating point)

[Hick Gunter]

> ...Even bankers, ever counting pennies, approximate to compute interest and 
> averages.  Little known fact: sometimes they compute interest on the basis of 
> a 360-day year.
>
>--jkl
>

Bankers calculate interest for 30 days per month, independent of the actual 
number of days, leaving 5 days of "no interest" per year. A similar practice 
was also seen in the Maya Haab (civil) Calendar, which has 18 months of 20 days 
each, plus a nineteenth "month" of 5 "bad hair days" (actually "bad luck 
days"). The background of the Maya Tzolkin (ritual) Calender with ist prominent 
bases 13 (months/year) and 20 (days/month and all multiples of years) is 
unclear.


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Reusing built-in sqlite3_io_methods

[Hick Gunter]

Reuse is only possible if your custom VFS's internal file object structure is 
compatible with struct unixFile as defined in os_unix.c und the selected 
locking strategy uses the same methods/system calls.

So my guess is "probably not", but grabbing code from os_unix.c might be a good 
starting point if your OS has similar system calls.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Albert Banaszkiewicz
Gesendet: Donnerstag, 20. Dezember 2018 11:31
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Reusing built-in sqlite3_io_methods

Hello everybody,

In our system we use our custom VFS implementation (sqlite3_vfs_register, 
sqlite3_vfs). Now we would like to come up with locking strategy.
And here is the question:
Is there a way to reuse one from the set of already implemented in sqlite ? Or 
is it necessary to write it from scratch or copy it from sqlite source code ?

Best Regards,
Albert Banaszkiewicz

___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] A Minor Issue Report: Extra const Keyword in PragmaName zName

[Hick Gunter]

There is a significant difference

A "const char *" is a (mutable) pointer to an immutable char. You can make it 
point somewhere else.

A "char * const" is an immutable pointer to a mutable char. You can change the 
char it is pointing to.

A "const char * const" is an immutable pointer to an immutable char. You can't 
change anything.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Green
Gesendet: Montag, 31. Dezember 2018 00:11
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] A Minor Issue Report: Extra const Keyword in 
PragmaName zName

I believe the struct PragmaName (in pragma.h) has an extra 'const'
keyword for zName, in Version 3.26.0 (2018-12-01); probably has no effect.

Currently,

/* Definitions of all built-in pragmas */ typedef struct PragmaName {
   const char *constzName; /* Name of pragma */
   u8 ePragTyp; /* PragTyp_XXX value */
   u8 mPragFlg; /* Zero or more PragFlg_XXX values */
   u8 iPragCName;   /* Start of column names in pragCName[] */
   u8 nPragCName;   /* Num of col names. 0 means use pragma name */
   u64 iArg;/* Extra argument */ } PragmaName;


Probably should be,

/* Definitions of all built-in pragmas */ typedef struct PragmaName {
   const char *zName;   /* Name of pragma */
   u8 ePragTyp; /* PragTyp_XXX value */
   u8 mPragFlg; /* Zero or more PragFlg_XXX values */
   u8 iPragCName;   /* Start of column names in pragCName[] */
   u8 nPragCName;   /* Num of col names. 0 means use pragma name */
   u64 iArg;/* Extra argument */ } PragmaName;



Thank you,

Richard Green


___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: A Minor Issue Report: Extra const Keyword in PragmaName zName

[Hick Gunter]

Please note that this is in a typedef. The initialization needs to happen when 
a structure of this type is declared.

const PragmaName g_mypragma = { "mypragma", ...};

Would allow the compiler to put *both* the string "mypragma" *and* the 
structure g_mypragma into (read only) "string space", with the linker/loader 
calculating the actual value of the pointer.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Mittwoch, 02. Jänner 2019 14:22
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] A Minor Issue Report: Extra const Keyword in 
PragmaName zName

On Wed, Jan 2, 2019 at 1:47 PM Richard Damon 
wrote:

> On 12/30/18 6:10 PM, Richard Green wrote:
> const char *const zName; // Note extra space
>
> Then that is declaring that zName is an immutable pointer to a
> immutable string/character, which is actually likely true, as the code
> shouldn't be changing the names of pragmas.
>

Yeah, but a const T*const pointer must be initialized.
(unless it's a function argument, in which case it's the "caller" doing the 
init, somehow).
Otherwise how else would you assign a value to that const pointer?

So as-is as a struct field, it wouldn't make sense.
C has no constructor like C++ has, to perform the assignment in the 
"initializer list"
of the Ctor (i.e. between : and { ) (maybe aggregate initialization can, that's 
as close to a Ctor C has. May that would work...)

In Java you can, it's called a "blank final", i.e. compiler tracks at 
compile-time when the variable is first assigned, allowing that one, and errors 
out at all other assignments (or if not initialized at all).

But in C/C++, I logically don't see how that makes sense. Haven't tried it 
recently though :). --DD ___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Optimisation opportunity on subquery?

[Hick Gunter]

It would be easier to reproduce if you had checked the provided sample code for 
errors first...

.) Error: near ")": syntax error
.) Error: cannot join using column item_id - column not present in both tables


Your first query specifies a full table scan over the users table (via the 
covering index) to create an intermediate table of counts per item_id, and then 
selects only one row.

Your second query specifies a partial index scan to create an intermediate 
table of 1 row with the required item_id, which is then joined.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jonathan Moules
Gesendet: Mittwoch, 02. Jänner 2019 17:44
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Optimisation opportunity on subquery?

Hi List,

The below seems to my very-non-expert mind like there's scope for query-plan 
optimisation.

I have two tables (simplified below):

CREATE TABLE users (
 item_id   TEXT REFERENCES item_info (item_id)
   NOT NULL
   COLLATE NOCASE,
 some_data TEXT,
);

CREATE INDEX users__item_id__idx ON users (
 item_id
);

CREATE TABLE item_info (
 item_id   TEXT PRIMARY KEY ON CONFLICT IGNORE
   NOT NULL
   COLLATE NOCASE,
 more_data TEXT
);

There are about 1 million records in users and 100,000 records in item_info.

These queries are all fast, taking about 0.002s:
 select * from item_info where item_id = ?;

 select count(1) from users group by item_id;

 select count(1) from users where item_id = ?;

But when I try and join them together, they're much slower at about 0.5s.
 SELECT
 *
 FROM
 item_info
 JOIN (select count(1) from users group by item_id)
 USING (item_id)
 where item_id = ?;

I kind of expected SQLite would figure out that the outer WHERE clause also 
applies to the subquery given the combination of USING and GROUP BY means it 
has to apply anyway.

If I explicitly include the WHERE inside the subquery, the EXPLAIN QUERY PLAN 
is identical, but it's back to the expected fast speed (0.002s):
 SELECT
 *
 FROM
 item_info
 JOIN (select count(1) from users WHERE item_id = ?)
 USING (item_id)
 where item_id = ?;

sqlite 3.24.0

Cheers,

Jonathan



___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Optimisation opportunity on subquery?

[Hick Gunter]

Note that you may pass different item_id values to the second query, which 
would then return 0 rows, but quickly. If you need to reference the same 
parameter in more than one location inside the SQL Statement, use explicit 
names or numbers

 SELECT
 *
 FROM
 item_info
 JOIN (select count(1) from users WHERE item_id = ?1)
 USING (item_id)
 where item_id = ?1;


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Hick Gunter
Gesendet: Donnerstag, 03. Jänner 2019 07:48
An: 'SQLite mailing list' 
Betreff: Re: [sqlite] [EXTERNAL] Optimisation opportunity on subquery?

It would be easier to reproduce if you had checked the provided sample code for 
errors first...

.) Error: near ")": syntax error
.) Error: cannot join using column item_id - column not present in both tables


Your first query specifies a full table scan over the users table (via the 
covering index) to create an intermediate table of counts per item_id, and then 
selects only one row.

Your second query specifies a partial index scan to create an intermediate 
table of 1 row with the required item_id, which is then joined.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jonathan Moules
Gesendet: Mittwoch, 02. Jänner 2019 17:44
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Optimisation opportunity on subquery?

Hi List,

The below seems to my very-non-expert mind like there's scope for query-plan 
optimisation.

I have two tables (simplified below):

CREATE TABLE users (
 item_id   TEXT REFERENCES item_info (item_id)
   NOT NULL
   COLLATE NOCASE,
 some_data TEXT,
);

CREATE INDEX users__item_id__idx ON users (
 item_id
);

CREATE TABLE item_info (
 item_id   TEXT PRIMARY KEY ON CONFLICT IGNORE
   NOT NULL
   COLLATE NOCASE,
 more_data TEXT
);

There are about 1 million records in users and 100,000 records in item_info.

These queries are all fast, taking about 0.002s:
 select * from item_info where item_id = ?;

 select count(1) from users group by item_id;

 select count(1) from users where item_id = ?;

But when I try and join them together, they're much slower at about 0.5s.
 SELECT
 *
 FROM
 item_info
 JOIN (select count(1) from users group by item_id)
 USING (item_id)
 where item_id = ?;

I kind of expected SQLite would figure out that the outer WHERE clause also 
applies to the subquery given the combination of USING and GROUP BY means it 
has to apply anyway.

If I explicitly include the WHERE inside the subquery, the EXPLAIN QUERY PLAN 
is identical, but it's back to the expected fast speed (0.002s):
 SELECT
 *
 FROM
 item_info
 JOIN (select count(1) from users WHERE item_id = ?)
 USING (item_id)
 where item_id = ?;

sqlite 3.24.0

Cheers,

Jonathan



___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Optimisation opportunity on subquery?

[Hick Gunter]

>-Ursprüngliche Nachricht-
>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
>Auftrag von Keith Medcalf
>On Wednesday, 2 January, 2019 16:58, Jonathan Moules 
>mailto:jonathan-li...@lightpear.com>> wrote:

>

>>Gah, sorry. Another typo. I really should be more awake when I post to

>>this list. The non-simplified code does have the item_id on the

>>subquery (otherwise it simply wouldn't execute at all of course). So:

>>

>>SELECT *

>>   FROM item_info

>>   JOIN (

>> select

>>   count(1) as num,

>>   item_id

>> from users

>> group by item_id)

>>  USING (item_id)

>>  where item_id = ?;

>

>Now perhaps we are getting somewhere.  So now what exactly is your complaint?

>



He first asked for (1) "compute all the totals and then select the one matching 
the item I provide" (table JOIN covering index full scan)



He later asked for (2) "compute the total for item#1 and then select it if it 
matches item#2" (table JOIN partial index scan), which is faster and returns 
the correct value only if item#1 == item#2 at run time.


He wanted (3) "select the item I provide and then compute a total for it" 
(select correlated subquery)



His complaint is that SQLite rightly declines to transform (1) into (3), 
instead of doing what he asked for and not what he wanted.





___
Gunter Hick | Software Engineer | Scientific Games International 
GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, 
HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Concatenating text literals with NULL yields NULL

[Hick Gunter]

Documented here: https://sqlite.org/opcode.html


Concat

Add the text in register P1 onto the end of the text in register P2 and store 
the result in register P3. If either the P1 or P2 text are NULL then store NULL 
in P3.
P3 = P2 || P1
It is illegal for P1 and P3 to be the same register. Sometimes, if P3 is the 
same register as P2, the implementation is able to avoid a memcpy().






-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Freitag, 04. Jänner 2019 10:48
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] Concatenating text literals with NULL yields NULL



I was just surprised by this behavior, see below.

Googling it, seems like SQL Server has a setting the change the behavior in 
that case.

Is this standard SQL behavior, as implemented in SQLite?

Not complaining, just asking whether I can depend on it, or not.



Thanks, --DD



C:\Users\ddevienne>sqlite3

SQLite version 3.25.3 2018-11-05 20:37:38 Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table t(v);

sqlite> insert into t values (null);

sqlite> select v from t;



sqlite> select '('||v||')' from t;



sqlite> select typeof('('||v||')') from t;

null

___

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 | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, 
HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] json_group_array() and sorting

[Hick Gunter]

I don't recall that any (aggregate) function is concerned at all about the 
order in which rows are visited. The effect is only visible in non-commutative 
aggregates (e.g. concatenation).

If you want the arguments presented to an aggregate function in a specific 
order, then you need to enforce that order, with an order by clause in a 
subselect if necessary.

If you have an order by clause which is already fulfilled by the visitation 
order, SQLite will not sort again.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Eric Grange
Gesendet: Dienstag, 08. Jänner 2019 09:17
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] json_group_array() and sorting

Hi,

Is json_group_array() supposed to honor a sorting clause or not ? (and 
concatenation aggregates in general)

I have a query like

select json_group_array(json_object(
   'id', st.id,
   'num', st.numeric_field,
   ...bunch of fields here...
))
from some_table st
...bunch of joins here...
where ...bunch of conditions...
order by st.numeric_field desc
limit 50


but the resulting JSON array is not ordered according to the "order by", but 
AFAICT by the st.id field (a primary key) When not aggregating, the records are 
in the correct order.

Is it a bug or something expected ?

I can get the proper order when I use a subquery for the joins & filters, and 
aggregate in a top level query, but that is rather more verbose, and I am not 
sure the ordering being preserved in that case is not just 'circumstancial' and 
could be affected by future SQLite query optimizations.

Thanks!

Eric
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLite 3.24.0 regression from enhanced "ALTER TABLE true/false DEFAULT" on TEXT field

[Hick Gunter]

Please retry with correct string quoting, i.e. single quotes. Double quotes are 
reserved for identifiers and interpreted as strings only if there is no such 
identifier.

"false" (double quotes) is an identifier, 'false' (single quotes) is a string.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Christian Nols
Gesendet: Freitag, 11. Jänner 2019 13:03
An: sqlite-users@mailinglists.sqlite.org
Cc: Mcl development 
Betreff: [EXTERNAL] [sqlite] SQLite 3.24.0 regression from enhanced "ALTER 
TABLE true/false DEFAULT" on TEXT field

From "Enhance ALTER TABLE so 
that it recognizes "true" and "false" as valid arguments to DEFAULT" introduced 
in 3.24.0

A TEXT field can no longer have "true"/"false" default values.
The smart DEFAULT conversion should not be triggered if the hint is TEXT.

Although SQLite is typeless, table datatype hint could be use in this use 
scenario.

Steps to reproduce
sqlite> CREATE TABLE "Table_1" ([ID] TEXT DEFAULT "false", [Field_1]
sqlite> TEXT NULL); insert into "Table_1" (Field_1) VALUES ("test");
sqlite> select * from Table_1;
0|test

Expected result
false|test

Christian Nols
Software Engineer

christian.n...@mcl-technologies.com
Tel : + 32 2 724 35 19

MCL
NYSDAM Building
avenue Reine Astrid 92, 3rd floor
1310 La Hulpe- Belgium
BCE registered number 2.087.817.310
Tel : + 32 2 724 35 00 - Fax : + 32 2 724 35 04 
www.mcl-collection.com

___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

[Hick Gunter]

With journal mode, SQLite supports 1 writer OR n readers; with WAL mode, SQLite 
supports 1 writer AND N readers.

In any case, connections need to indicate if or how long they are willing to 
wait for the db file to be unlocked. Default is NO.

The easiest way is to specify a timeout on the connection. The value needs to 
be longer than your longest write transaction is expected to run and shorter 
than the latency required by your application.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Urs Wagner
Gesendet: Montag, 14. Jänner 2019 10:24
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] SQLite error (5): database is locked

Hallo

I use several tasks in C# to call Sqlite queries.
No I get the error SQLite error (5): database is locked.
Is it not possible to use more than one tasks with Sqlite?

Regards

Urs
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

[Hick Gunter]

Please note the the semicolon at the end of an SQL Statement is required. 
"PRAGMA busy_timeout = 100" is not complete SQL.
Also, the schema prefix needs to be replaced with the attach name of the 
database whose properties you wish to query/change. It may be omitted if "main" 
is desired.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Urs Wagner
Gesendet: Montag, 14. Jänner 2019 12:14
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

We are using entity framework

The timeout pragma does not work. Is think the timeout is not set, see below

var esb = new EntityConnectionStringBuilder
{
Metadata = 
"res://*/RadaxModel.csdl|res://*/RadaxModel.ssdl|res://*/RadaxModel.msl",
Provider = "System.Data.SQLite.EF6",
ProviderConnectionString = @"data source=" + _dataBase + 
";PRAGMA foreign_keys = ON;PRAGMA locking_mode = EXCLUSIVE;PRAGMA 
schema.synchronous = NORMAL; PRAGMA schema.journal_mode = DELETE; PRAGMA 
busy_timeout = 10"
};


-Original Message-
From: sqlite-users  On Behalf Of 
Hick Gunter
Sent: Monday, January 14, 2019 11:28 AM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

With journal mode, SQLite supports 1 writer OR n readers; with WAL mode, SQLite 
supports 1 writer AND N readers.

In any case, connections need to indicate if or how long they are willing to 
wait for the db file to be unlocked. Default is NO.

The easiest way is to specify a timeout on the connection. The value needs to 
be longer than your longest write transaction is expected to run and shorter 
than the latency required by your application.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Urs Wagner
Gesendet: Montag, 14. Jänner 2019 10:24
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] SQLite error (5): database is locked

Hallo

I use several tasks in C# to call Sqlite queries.
No I get the error SQLite error (5): database is locked.
Is it not possible to use more than one tasks with Sqlite?

Regards

Urs
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Feature req: Shell .open command should inform user what file it opened

[Hick Gunter]

IIRC SQLite will create a new database file only if (and when) the user enters 
a command that actually accesses the file. This avoids creating empty files if 
the user mistypes the file name. So the desired output might be required to 
appear "later".

Use the already implemented flag -readonly to return an error if the file does 
not exist.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von bob_sql...@mail.com
Gesendet: Dienstag, 22. Jänner 2019 15:29
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Feature req: Shell .open command should inform 
user what file it opened

In the command shell, the .open command will either open an existing file or 
create a new one if it doesn't already exist. This can be confusing.

It's even more confusing on Windows if the user has forgotten that the path 
should be written with forward slashes instead of the usual Windows backslashes.

This is the situation that baffled me for some time today. Backslashes in the 
path are ignored. So my request to open an existing file was changed to a 
request to open a new file in the default directory. I didn't have permission, 
so Windows put it into the VirtualStore instead.
There was no error message. All I knew was that I was unable to access the data 
as expected. Even the .databases command displayed what appeared to be the 
correct path to the file including the backslashes.

All I'm asking is that the .open command gives a simple response saying if it 
created a new file or opened an existing one and display the actual path and 
filename. This would make it obvious what file had actually been opened. With 
the above user error the directory would display as the default directory and 
the filename would contain all the elements of the user's intended path 
squished together. The message would look something like this.

"Opened new file C:/Program Files/SQLite/UsersBobDocumentsProgTest.sqlite"

The equivalent message if the user had entered the path correctly would be.

"Opened existing file C:/Users/Bob/Documents/Prog/Test.sqlite"

This would make it clear to the user that the wrong file had been opened.
It seems easily do-able. Hope this helps.

Bob
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Inconsistent behavior in sqlite3_set_authorizer() and error messages

[Hick Gunter]

From https://www.sqlite.org/c3ref/c_alter_table.html

"The 5th parameter to the authorizer callback is the name of the database 
("main", "temp", etc.) if applicable."

Is that not the case in your tests?

I guess the difference between "CREATE TEMP TABLE x" and "CREATE TABLE temp.x" 
ist hat the former already knows that the action is "create a temporary table" 
before the table name is parsed, whereas the latter has to check the schema 
name first. Note that you can do "CREATE TEMP TABLE temp.x".

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Joshua Thomas Wise
Gesendet: Montag, 04. Februar 2019 04:19
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Inconsistent behavior in sqlite3_set_authorizer() 
and error messages

Hello,

I found some behaviors that should probably be considered bugs (and should be 
fixed and/or documented).

Let’s start the explanation by observing some behavior that actually is correct 
and consistent. Below, we observe which type of action is reported by 
sqlite3_set_authorizer(), given some SQL input:

"CREATE TEMP TABLE foo(x)” -> SQLITE_CREATE_TEMP_TABLE "CREATE TEMP VIEW 
foo AS SELECT 1” -> SQLITE_CREATE_TEMP_VIEW "CREATE TEMP TRIGGER foo BEFORE 
INSERT ON t BEGIN SELECT 1; END" -> SQLITE_CREATE_TEMP_TRIGGER

So far so good. But what happens when we use the “temp.foo” syntax instead of 
the TEMP keyword?

"CREATE TABLE temp.foo(x)" -> SQLITE_CREATE_TABLE "CREATE INDEX temp.foo ON 
t(x)" -> SQLITE_CREATE_TEMP_INDEX "CREATE VIEW temp.foo AS SELECT 1" -> 
SQLITE_CREATE_VIEW "CREATE TRIGGER temp.foo BEFORE INSERT ON t BEGIN SELECT 1; 
END" -> SQLITE_CREATE_TEMP_TRIGGER

Uh-oh. We have some inconsistencies there. CREATE TABLE and CREATE VIEW report 
their regular CREATE_* variants, while CREATE INDEX and CREATE TRIGGER do 
report their TEMP_* variants.

I recommend that either all or none of those statements should report their 
TEMP_* variants to the sqlite3_set_authorizer() callback. If the decision is 
made to not use TEMP_* variants in these cases, the schema name (“temp”, in 
this case) should ideally be included as an argument to the 
sqlite3_set_authorizer() callback.

I also found strange inconsistencies regarding error messages. If we execute 
the 4 statements above, but this time with the SQLITE_OMIT_TEMPDB compile-time 
option present, we get the following error messages (respectively):

'SQL logic error'
'cannot create a TEMP index on non-TEMP table “t”'
'SQL logic error’
'SQL logic error’

However, if we replace “temp.foo” with “miss.foo” in each of those statements, 
we get the much better error message:

'unknown database miss’

All of the observations described in this email were very surprising to me. 
Hopefully they can be fixed and/or documented.

Best regards,

Josh


___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Bug due to left join strength reduction optimization?

[Hick Gunter]

We have an input table named tab with a single row whose sole column named id 
as a value of 1.
This table is joined to itself in a left join. The cartesian product would be

tab.id  tab2.id
1   1

The clause ON 0 evaluates to FALSE, so the row is eliminated from the result 
set.
LEFT JOIN processing requires that the result set be extended by a special row 
for each lhs row, giving

tab.id  tab2.id
1   NULL

From this the result expression evaluates to (NULL IS NOT NULL) which gives the 
number 0.
Then the WHERE clause is evaluated to (0 = 0) which gives TRUE.

This yields a single result row with a value 0

Changing the order of the tables gives

tab2.id tab.id
1   1

Which devolves into

tab2.id tab.id
1   NULL

Yielding c (1 IS NOT NULL) which gives the number 1
Which means the row is excluded by the WHERE clause

No output

Testing of R.Smiths changed query reveals that the difference is caused by 
different handling of the equality and the IS operator.

asql> explain query plan select 1 from tab left join tab as tab2 on 0 where 
(tab2.id IS NOT NULL) = 0;
idparent notu  deta
  -    
4 0  0 SCAN TABLE tab AS tab2
110  0 SCAN TABLE tab
asql> explain query plan select 1 from tab left join tab as tab2 on 0 where 
(tab2.id IS NOT NULL) is 0;
idparent notu  deta
  -    
3 0  0 SCAN TABLE tab
5 0  0 SCAN TABLE tab AS tab2


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Dienstag, 05. Februar 2019 09:14
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Bug due to left join strength reduction 
optimization?

On Tue, Feb 5, 2019 at 7:47 AM Keith Medcalf  wrote:

> sqlite> select ... from tab left join tab as tab2 on 0 ...
>

Can someone please educate me on this {{ on 0 }} join "condition" ?

I'm not following what the intended meaning is... Must have one, since OP 
"expects 1 row with one column containing 0", despite a table with a single
1 row.

Thanks, --DD
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Inconsistent behavior in sqlite3_set_authorizer() and error messages

[Hick Gunter]

I take it that this is a side effect of how the parser works:

Given the sequence 'CREATE TEMP TABLE X ...', the parser knows that the 
required action is SQLITE_CREATE_TEMP_TABLE during prepare of the statement as 
soon as the token TABLE is accepted.

OTOH, given the sequence 'CREATE TABLE TEMP.x', the parser knows that the 
required action is SQLITE_CREATE_TABLE during prepare of the statement as soon 
as the token TABLE is accepted.  Apparently, this is not updated when the 
schema name is parsed.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Joshua Thomas Wise
Sent: Sonntag, 10. Februar 2019 01:17
To: sqlite-users@mailinglists.sqlite.org
Subject: [EXTERNAL] Re: [sqlite] Inconsistent behavior in 
sqlite3_set_authorizer() and error messages

I’ll interpret the silence as a “no”?


> On Feb 7, 2019, at 11:53 AM, Joshua Thomas Wise  
> wrote:
>
> Is the SQLite team aware of these issues?
>
>
>> On Feb 4, 2019, at 7:00 AM, sqlite-users-requ...@mailinglists.sqlite.org 
>>  wrote:
>>
>> Hello,
>>
>> I found some behaviors that should probably be considered bugs (and should 
>> be fixed and/or documented).
>>
>> Let’s start the explanation by observing some behavior that actually is 
>> correct and consistent. Below, we observe which type of action is reported 
>> by sqlite3_set_authorizer(), given some SQL input:
>>
>> "CREATE TEMP TABLE foo(x)” -> SQLITE_CREATE_TEMP_TABLE "CREATE
>> TEMP VIEW foo AS SELECT 1” -> SQLITE_CREATE_TEMP_VIEW "CREATE
>> TEMP TRIGGER foo BEFORE INSERT ON t BEGIN SELECT 1; END" ->
>> SQLITE_CREATE_TEMP_TRIGGER
>>
>> So far so good. But what happens when we use the “temp.foo” syntax instead 
>> of the TEMP keyword?
>>
>> "CREATE TABLE temp.foo(x)" -> SQLITE_CREATE_TABLE "CREATE INDEX
>> temp.foo ON t(x)" -> SQLITE_CREATE_TEMP_INDEX "CREATE VIEW
>> temp.foo AS SELECT 1" -> SQLITE_CREATE_VIEW "CREATE TRIGGER
>> temp.foo BEFORE INSERT ON t BEGIN SELECT 1; END" ->
>> SQLITE_CREATE_TEMP_TRIGGER
>>
>> Uh-oh. We have some inconsistencies there. CREATE TABLE and CREATE VIEW 
>> report their regular CREATE_* variants, while CREATE INDEX and CREATE 
>> TRIGGER do report their TEMP_* variants.
>>
>> I recommend that either all or none of those statements should report their 
>> TEMP_* variants to the sqlite3_set_authorizer() callback. If the decision is 
>> made to not use TEMP_* variants in these cases, the schema name (“temp”, in 
>> this case) should ideally be included as an argument to the 
>> sqlite3_set_authorizer() callback.
>>
>> I also found strange inconsistencies regarding error messages. If we execute 
>> the 4 statements above, but this time with the SQLITE_OMIT_TEMPDB 
>> compile-time option present, we get the following error messages 
>> (respectively):
>>
>> 'SQL logic error'
>> 'cannot create a TEMP index on non-TEMP table “t”'
>> 'SQL logic error’
>> 'SQL logic error’
>>
>> However, if we replace “temp.foo” with “miss.foo” in each of those 
>> statements, we get the much better error message:
>>
>> 'unknown database miss’
>>
>> All of the observations described in this email were very surprising to me. 
>> Hopefully they can be fixed and/or documented.
>>
>> Best regards,
>>
>> Josh
>

___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Choosing one value from the group with priority

[Hick Gunter]

There is some magic in SQLite regarding non-aggregate columns in an aggregate 
query:

If there is a single MIN or MAX aggregate function in the GROUP BY, then the 
non-aggegate columns will be chosen from tha particular row

SELECT id, MIN(source_id),value FROM test WHERE id IN (1,2) GROUP BY 1;

Returns the value from the record with the smallest source_id within the group.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Ivan Krylov
Sent: Dienstag, 12. Februar 2019 12:13
To: sqlite-users@mailinglists.sqlite.org
Subject: [EXTERNAL] [sqlite] Choosing one value from the group with priority

Hi!

I have a table of some values obtained from different sources:

create table test (
id, source_id, value,
primary key(id, source_id)
);
insert into test values
(1, 1, 11), (1, 2, 12), (1, 3, 13),
(2, 1, 21),
(3, 2, 32);

When SELECTing the values, I would like to filter by id and get only one value 
per id. This part is misleadingly easy:

select * from test where id in (1,2) group by id;

but then I don't get to control which source I'm obtaining the values from 
(when there is more than one). Let's assume for now that I prefer to choose 
values with a particular source_id, but if those are not present, I would take 
what's available. I managed to invent a query which would assign a priority to 
each value using window functions:

select
*, row_number() over win
from test
where
id in (1,2)
window win as (
partition by id
order by abs(source_id-3)
);

By subtracting a different value in the ORDER BY ABS(...) clause, I can force a 
value with a different source_id to come up first.

And since SQLite doesn't currently allow putting window functions in the WHERE 
clause, I'm using a nested query to actually get the values with the highest 
priority:

select id, value from (
select
id, value, row_number() over win as priority
from test
where
id IN (1,2)
window win as (
partition by id
order by abs(source_id-3)
)
) where priority = 1;

which results in the following query plan:

QUERY PLAN
|--CO-ROUTINE 1
|  |--CO-ROUTINE 3
|  |  |--SEARCH TABLE test USING INDEX sqlite_autoindex_test_1 (id=?)
|  |  `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
|  `--SCAN SUBQUERY 3
`--SCAN SUBQUERY 1

Is this the most effective way to express my query? Can the more general 
problem of assigning a priority to all sources (e.g. "I want records from 
source_id 3, otherwise 1, otherwise 2") be solved in a similar way?

--
Best regards,
Ivan
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Opening a DataBase file with a custom "user data" parameter

[Hick Gunter]

Use the URI format to specify the name of your cipher object and dynamically 
locate it within your vfs code.

e.g. ATTACH 'file:custom.db?vfs=myvfs&cipher=mylib:entry' as secret;

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Esenthel
Sent: Donnerstag, 21. Februar 2019 04:31
To: sqlite-users@mailinglists.sqlite.org
Subject: [EXTERNAL] [sqlite] Opening a DataBase file with a custom "user data" 
parameter

I'm trying to use "sqlite3_open_v2" with a custom VFS (file system), and pass a 
pointer "void *user_data" to the sqlite3_open_v2 function, so it gets passed 
down to the "sqlite3_vfs::xOpen"
However there's no option for that, so how to do that?

The reason is that for opening files in the custom callback, I need to use a 
pointer to some helper "cipher" class object, to allow my own 
encryption/decryption.

However xOpen accepts only "const char *zName".

I have one custom global VFS, and I want to use different ciphers / user data 
for each database I use.

What would solve the problem:
add new "void *user_data" parameter to functions "sqlite3_open_v2", and 
"sqlite3_vfs::xOpen".

Thanks,

Greg
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] WITHOUT ROWID tables with a ROWID

[Hick Gunter]

The magic aliasing of rowid to the internal "record number" of a rowid table 
only works for a single row declared as exactly INTEGER PRIMARY KEY and in a 
rowid table.

If a table contains a field name rowid that has any different definition (even 
INT PRIMARY KEY), it will be just an ordinary column; for rowid tables, the 
internal record number can stillbe accessed via the 'oid' and '_rowid_' names.

T1: rowid = record number
T2: rowid not declared, record number does not exist
T3: rowid is the key to a clustered index, has TEXT affinity; there is no 
record number
T4: rowid is an ordinary field, use oid to access the record number
T5: rowid is the key to a clustered index, has INTEGER affinity; there is no 
record number

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Tom Bassel
Sent: Donnerstag, 21. Februar 2019 21:02
To: sqlite-users@mailinglists.sqlite.org
Subject: [EXTERNAL] [sqlite] WITHOUT ROWID tables with a ROWID

Hi, this is my first post to this group. I am not trying to be difficult. I 
work on an app that uses SQLite as the underlying database engine. At one time 
years ago I remember assuming that the ROWID column in a table in SQLite would 
always be an integer. However, I can see a user who is not familiar with SQLite 
create a table with a column named "rowid" that is not an integer (in table t4 
below). Are tables t3, t4, and t5 below working as designed?

Thanks and I really enjoy using SQLite and reading this mailing list.
Tom

SQLite version 3.27.1 2019-02-08 13:17:39 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t1(nonrowid);
sqlite> insert into t1(rowid, nonrowid) values ('pluto', 'venus');
Error: datatype mismatch
sqlite> create table t2(nonrowid text primary key) without rowid; insert
sqlite> into t2 values('sun'); select rowid, * from t2;
Error: no such column: rowid
sqlite> create table t3(rowid text primary key) without rowid; insert
sqlite> into t3 values('moon'); select rowid, * from t3;
moon|moon
sqlite> create table t4(rowid text primary key); insert into t4
sqlite> values('earth'); select rowid, * from t4;
earth|earth
sqlite> create table t5(rowid integer primary key) without rowid; insert
sqlite> into t5 values('mars'); select rowid, * from t5;
mars|mars

___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Find if an upsert operation did an insert or an update.

[Hick Gunter]

The mission statement of the upsert is to fulfil the assertion that a row 
exists and the desired change has been recorded therein, while hiding the 
details (new row inserted or old row updated) from the caller. Why do you think 
you need this information? As already suggested, use a transaction to retrieve 
the row; update if found and insert if not.



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Constantine Yannakopoulos
Sent: Freitag, 22. Februar 2019 17:08
To: SQLite mailing list 
Subject: [EXTERNAL] [sqlite] Find if an upsert operation did an insert or an 
update.

Hello,

I would like to find whether an upsert operation actually did an insert or an 
update, preferably without having to execute extra SQL statements before or 
after it. I thought of using last_insert_rowid() before and after the upsert 
and check if the result has changed but while this seems to work for normal 
tables it will not work for WITHOUT ROWID tables. Is there another way that 
works consistently both with tables with or without rowid?

Best regards,

Constantine
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Bug: table-valued functions for PRAGMA ignore database names

[Hick Gunter]

While it does not provide an example for attached databases, this statement 
from documentation seems to apply:

"The PRAGMA argument and schema, if any, are passed as arguments to the 
table-valued function."

Maybe you should be doing something similar to

Select pragma_user_version('main'), pragma_user_version('test_db');

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Gunold
Sent: Freitag, 22. Februar 2019 14:29
To: sqlite-users@mailinglists.sqlite.org
Subject: [EXTERNAL] [sqlite] Bug: table-valued functions for PRAGMA ignore 
database names

Hello,

I found a bug in sqlite when using table-values pragma functions with attached 
databases when using the sqlite3.exe for windows.
In my tests I found out, that it is not possible to use table-valued pragma 
functions on attached databases e.g. to read user_version or schema_version.

So the Query:

SELECT a.user_version as 'main_version', b.user_version as 'attached_version' 
FROM
  main.pragma_user_version() as 'a',
   test_db.pragma_user_version() as 'b';

Should display the user_version of main-database and attached database as wel, 
but both values display always the version of the main database.

I tested this with 2 szenarios, first with attaching a in-memory database and 
second with attaching a physical database.
Results are identical for both.




Infos about test envoirement

OS:Windows XP Professional Service Pack 3 (32-Bit)
Sqlite-Version:3.27.1 2019-02-08 13:17:39
0eca3dd3d38b31c92b49ca2d311128b74584714d9e7de895b1a6286ef959a1dd
Test-Command:sqlite3 -echo bug_test.db < pragma_bug-testcase.sql



To make it easy to reconstruct this Bug I added example outputs and my 
inputs-commands (as sql file) for sqlite3.exe.

Simply run
"sqlite3 -echo bug_test.db < pragma_bug-testcase.sql"

on any test-databse and compare results to my "test-ouputs" for szenario 1 and 
szenario 2.


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users