Re: [sqlite] How to use sqlite3_table_column_metadata?

[petern]

Hi Bart.  Questions like that can be answered by looking at the body of the
function in your copy of the source code.  In this case, the call will
accept null column name and then later set default values for fields like
so:

if( pCol ){
zDataType = sqlite3ColumnType(pCol,0);
zCollSeq = pCol->zColl;
notnull = pCol->notNull!=0;
primarykey  = (pCol->colFlags & COLFLAG_PRIMKEY)!=0;
autoinc = pTab->iPKey==iCol && (pTab->tabFlags & TF_Autoincrement)!=0;
  }else{
zDataType = "INTEGER";
primarykey = 1;
  }

That else block is originating your phantom values without specifying a
column name but I don't think there is any cosmic significance.  Earlier in
the same function body there are also some comments:

/* Find the column for which info is requested */
  if( zColumnName==0 ){
/* Query for existance of table only */
  }else{

Be thankful for comments!

Peter

On Mon, Nov 27, 2017 at 5:32 PM, Bart Smissaert 
wrote:

> One strange thing I noticed with this:
>
> If I pass a valid connection  and  valid table name but null for all other
> input parameters I get:
>
> return value:  0
> data type: INTEGER
> collation:   BINARY
> not null: 0
> part of primary key:  1
> auto-increment:0
>
> How does that work?
>
> RBS
>
> On Mon, Nov 27, 2017 at 10:13 AM, Bart Smissaert  >
> wrote:
>
> > > My advise to the VB guy would be to load the extension instead of
> trying
> > to
> > get VB to marshal pointers from the C API.
> >
> > ?? I do load the extension although it is compiled std_call, so VB6 can
> > talk to it.
> >
> > RBS
> >
> >
> > On Mon, Nov 27, 2017 at 12:59 AM, petern 
> > wrote:
> >
> >> I pasted that SQL into a SQLite shell and tried it out.
> >>
> >> First off,  Affinity (aka pragma table_info.type) column is case
> collated,
> >> so the LIKE operator should be used:
> >>
> >> select O.*
> >>   from SysColumns as O
> >>  where ObjectType == 'table'
> >>and IsPrimaryKey == 1
> >>and Affinity LIKE 'INTEGER'
> >>and not exists (select 1
> >>  from SysColumns as I
> >> where I.ObjectType == O.ObjectType
> >>   and I.ObjectName == O.ObjectName
> >>   and I.IsPrimaryKey > 1);
> >>
> >> [Side question. Do you find the '==' operator more readable/informative
> >> than the plain '=' SQL convention?]
> >>
> >> The main problem is still AUTOINCREMENT. You may not use AI, but a tool
> >> that reads others' schemas has to deal with that possibility.  As well,
> >> the
> >> system table sqlite_sequence will have no rows about an AI column when
> >> that
> >> AI table has no rows.  Therefore, the only general way, without calling
> >> the
> >> internal parser as I did in the extension, is to partly parse the SQL of
> >> the CREATE TABLE statement.
> >>
> >> Partial parsing isn't too hard I suppose.  There are a couple facts
> about
> >> the well formed CREATE statement that help.
> >>
> >> 1. The keywords PRIMARY KEY must be two words.
> >> 2. The keyword "AUTOINCREMENT" must come after "KEY" and before the next
> >> comma or closing bracket.
> >>
> >> From those facts it should be feasible to deduce the AUTOINCREMENT
> status
> >> by use of the instr() and substr() functions.
> >>
> >> Or, one could simply use the native -std=c90 style SQLite extension I
> >> posted for the other Peter...
> >>
> >> My advise to the VB guy would be to load the extension instead of trying
> >> to
> >> get VB to marshal pointers from the C API.  Though, I suppose that isn't
> >> the worst idea from recent posts.  Reading all the rows of a table in
> >> Python to get the count() has to be the best one.  In fact, that one is
> a
> >> classic job interview question.
> >>
> >>
> >>
> >>
> >>
> >>
> >> On Sun, Nov 26, 2017 at 1:41 PM, Keith Medcalf 
> >> wrote:
> >>
> >> > Not an answer to what is wrong with your call to get the metadata,
> >> > however, the information is all available from SQL.
> >> >
> >> > Data Catalog Views:
> >> >
> >> > drop view SysIndexColumns;
> >> > drop view SysIndexes;
> >> > drop view SysColumns;
> >> > drop view SysObjects;
> >> >
> >> > create view if not exists SysObjects
> >> > as
> >> > select type as ObjectType,
> >> >name as ObjectName
> >> >   from sqlite_master
> >> >  where type in ('table', 'view', 'index');
> >> >
> >> > create view if not exists SysColumns
> >> > as
> >> > select ObjectType,
> >> >ObjectName,
> >> >cidas ColumnID,
> >> >name   as ColumnName,
> >> >type   as Affinity,
> >> >"notnull"  as IsNotNull,
> >> >dflt_value as DefaultValue,
> >> >pk as IsPrimaryKey
> >> >   from SysObjects
> >> >   join pragma_table_info(ObjectName);
> >> >
> >> > create view if not exists SysIndexes
> >> > as
> >> > select ObjectType,

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Keith Medcalf]

>Can you not deduce what you want using sqlite_sequence, as described
>in section 2.6.1 of

>

>?  This allows you to distinguish a primary key which happens to be
>an integer from an |NTEGER PRIMARY KEY.

No it does not.  It only identifies AUTOINCREMENT INTEGER PRIMARY KEY since 
only AUTOINCREMENT fields are in sqlite_sequence, only if a row has been 
inserted, and AUTOINCREMENT fields just happen to also have to be INTEGER 
PRIMARY KEY.

>Another useful thing might be to find the command used to create the
>table from the sqlite_master table and see whether it includes the
>text "INTEGER PRIMARY KEY".

Except that will not work.

create table x
(
  y INTEGER,
  z TEXT,
  PRIMARY KEY(y)
);

Also identifies y as the INTEGER PRIMARY KEY but does not contain the spelling 
"INTEGER PRIMARY KEY" anywhere within it.




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

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Simon Slavin]

On 28 Nov 2017, at 12:38am, Peter Halasz  wrote:

> Please can SQLite developers make the ROW ID status of a field visible in
> future versions?

If it’s cone, the obvious place would be to add a column to



Can you not deduce what you want using sqlite_sequence, as described in section 
2.6.1 of



?  This allows you to distinguish a primary key which happens to be an integer 
from an |NTEGER PRIMARY KEY.

Another useful thing might be to find the command used to create the table from 
the sqlite_master table and see whether it includes the text "INTEGER PRIMARY 
KEY".

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

Re: [sqlite] How to use sqlite3_table_column_metadata?

[Bart Smissaert]

One strange thing I noticed with this:

If I pass a valid connection  and  valid table name but null for all other
input parameters I get:

return value:  0
data type: INTEGER
collation:   BINARY
not null: 0
part of primary key:  1
auto-increment:0

How does that work?

RBS

On Mon, Nov 27, 2017 at 10:13 AM, Bart Smissaert 
wrote:

> > My advise to the VB guy would be to load the extension instead of trying
> to
> get VB to marshal pointers from the C API.
>
> ?? I do load the extension although it is compiled std_call, so VB6 can
> talk to it.
>
> RBS
>
>
> On Mon, Nov 27, 2017 at 12:59 AM, petern 
> wrote:
>
>> I pasted that SQL into a SQLite shell and tried it out.
>>
>> First off,  Affinity (aka pragma table_info.type) column is case collated,
>> so the LIKE operator should be used:
>>
>> select O.*
>>   from SysColumns as O
>>  where ObjectType == 'table'
>>and IsPrimaryKey == 1
>>and Affinity LIKE 'INTEGER'
>>and not exists (select 1
>>  from SysColumns as I
>> where I.ObjectType == O.ObjectType
>>   and I.ObjectName == O.ObjectName
>>   and I.IsPrimaryKey > 1);
>>
>> [Side question. Do you find the '==' operator more readable/informative
>> than the plain '=' SQL convention?]
>>
>> The main problem is still AUTOINCREMENT. You may not use AI, but a tool
>> that reads others' schemas has to deal with that possibility.  As well,
>> the
>> system table sqlite_sequence will have no rows about an AI column when
>> that
>> AI table has no rows.  Therefore, the only general way, without calling
>> the
>> internal parser as I did in the extension, is to partly parse the SQL of
>> the CREATE TABLE statement.
>>
>> Partial parsing isn't too hard I suppose.  There are a couple facts about
>> the well formed CREATE statement that help.
>>
>> 1. The keywords PRIMARY KEY must be two words.
>> 2. The keyword "AUTOINCREMENT" must come after "KEY" and before the next
>> comma or closing bracket.
>>
>> From those facts it should be feasible to deduce the AUTOINCREMENT status
>> by use of the instr() and substr() functions.
>>
>> Or, one could simply use the native -std=c90 style SQLite extension I
>> posted for the other Peter...
>>
>> My advise to the VB guy would be to load the extension instead of trying
>> to
>> get VB to marshal pointers from the C API.  Though, I suppose that isn't
>> the worst idea from recent posts.  Reading all the rows of a table in
>> Python to get the count() has to be the best one.  In fact, that one is a
>> classic job interview question.
>>
>>
>>
>>
>>
>>
>> On Sun, Nov 26, 2017 at 1:41 PM, Keith Medcalf 
>> wrote:
>>
>> > Not an answer to what is wrong with your call to get the metadata,
>> > however, the information is all available from SQL.
>> >
>> > Data Catalog Views:
>> >
>> > drop view SysIndexColumns;
>> > drop view SysIndexes;
>> > drop view SysColumns;
>> > drop view SysObjects;
>> >
>> > create view if not exists SysObjects
>> > as
>> > select type as ObjectType,
>> >name as ObjectName
>> >   from sqlite_master
>> >  where type in ('table', 'view', 'index');
>> >
>> > create view if not exists SysColumns
>> > as
>> > select ObjectType,
>> >ObjectName,
>> >cidas ColumnID,
>> >name   as ColumnName,
>> >type   as Affinity,
>> >"notnull"  as IsNotNull,
>> >dflt_value as DefaultValue,
>> >pk as IsPrimaryKey
>> >   from SysObjects
>> >   join pragma_table_info(ObjectName);
>> >
>> > create view if not exists SysIndexes
>> > as
>> > select ObjectType,
>> >ObjectName,
>> >name as IndexName,
>> >seq  as IndexID,
>> >"unique" as IsUniqueIndex,
>> >origin   as IndexOrigin,
>> >partial  as IsPartialIndex
>> >   from SysObjects
>> >   join pragma_index_list(ObjectName);
>> >
>> > create view if not exists SysIndexColumns
>> > as
>> > select ObjectType,
>> >ObjectName,
>> >IndexName,
>> >seqno  as IndexColumnSequence,
>> >cidas ColumnID,
>> >name   as ColumnName,
>> >"desc" as IsDescendingOrder,
>> >coll   as Collation,
>> >keyas IsPartOfKey
>> >   from SysIndexes
>> >   join pragma_index_xinfo(IndexName);
>> >
>> > Then
>> >
>> > select O.*
>> >   from SysColumns as O
>> >  where ObjectType == 'table'
>> >and IsPrimaryKey == 1
>> >and Affinity == 'integer'
>> >and not exists (select 1
>> >  from SysColumns as I
>> > where I.ObjectType == O.ObjectType
>> >   and I.ObjectName == O.ObjectName
>> >   and I.IsPrimaryKey > 1);
>> >
>> > will return all the 'INTEGER PRIMARY KEY' in the "main" database ...
>> >
>> > I do not know what it does for "without 

[sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Peter Halasz]

Disclaimer: I am not a developer on linq2db. Nor am I a developer on "DB
Browser for SQLite", nor am I a developer of any of the other tools which
help developers create SQLite databases and integrate them with their
projects through UIs and APIs built on SQLite and its metadata.

However, it's quite clear to me, as only a user of just a few of these
tools and libraries, that they would be improved greatly if they had a
direct way of querying which field in a SQLite table was acting as an alias
of ROW ID.

linq2db, for example, has no simple way of definitely telling whether a
field is a true INTEGER PRIMARY KEY, so its developers have left it as an
exercise for its library users to debug errors which come up as a result,
and to manually "correct column definitions". Something which can result in
much time wasted unnecessarily tracking down the bugs, finding special API
calls to work around them, and a less than favorable view of SQLite for its
documentation of these anomalies.

linq2db could, in theory, fix this issue. But it would take a bunch of
error-prone work to re-create SQLite's ROW ID rules, so they have chosen
not to. They have simply chosen to ignore the weird behavior that comes
form not differentiating an INTEGER PRIMARY KEY from an INT PRIMARY KEY. I
don't blame them for having other priorities. After all, linq2db integrates
with 12 other database engines. And this isn't about linq2db, it's about
every tool that comes across the need for this metadata.

Many other tools also have bugs or poor UX because they cannot access a
simple bit of metadata. This could be solved quite easily if SQLite simply
exposed this data.

I was honestly stunned when I discovered this basic information, something
which completely changes the behavior of a field, is not accessible to
users.

Please can SQLite developers make the ROW ID status of a field visible in
future versions?

It could be done either directly through a PRAGMA statement, or slightly
less directly by exposing "isRowId()" or "collseq()" style methods as
posted by peter.nichvolodov in the previous thread.

I really hope there is not resistance this idea. It's really amazing to me
that such a mature, battle-tested database engine does not fully expose its
metadata and field behavior.

Cheers

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

Re: [sqlite] sqlite3_get_auxdata() defect

[Stadin, Benjamin]

Hi Clemens,

Even though the documentation makes no guaranty about the lifetime, it is was 
also for me rather useless. 

I need to do some spatial calculations in my custom SQL function, and use the 
proj4 projection library within it. This SQL function is called very often, and 
having to recreate the proj4 object with projection params each time was a huge 
performance issue for me. I wasn’t able to figure out when the data was 
actually cached – during my tests I simply got the free callback fired 
immediately after each step. 

I also had a second use case which actually requires a guaranty for the aux 
data to exist (providing the current position of a 3D camera. Somewhat 
comparable to a date function – you get current data whenever you call it).

I eventually hacked the VDBE struct, added a pointer to it, and altered some 
places in order to be able to pass a pointer to the sqlite3_step which I then 
retrieve from the custom sql function from the context via something like 
sqlite3_get_custom_ptr. 

I would call my implementation a hack, though I simply required the performance 
improvement and the guaranty for the instance be available whenever the 
function gets called. It would be great if there was a (probably new) API which 
does that in a proper way.

Regards
Ben

Am 27.11.17, 22:28 schrieb "sqlite-users im Auftrag von Clemens Ladisch" 
:

petern wrote:
> Evidently the sqlite3_get_auxdata() API is very buggy.

The documentation says that
| under some circumstances the associated metadata may be preserved.

You have found circumstances where the metadata is not preserved.

This is not a bug.

I can see that you are unhappy, but that is just because you are trying
to make it do something that it was never intended to do.

If you want a function with mutable state over multiple invocations,
do not use sqlite3_set_auxdata() but an aggregation function, or store
the data elsewhere.


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


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

Re: [sqlite] sqlite3_get_auxdata() defect

[petern]

Clemens,

Indeed.  It doesn't work as a basis for reliable performance improvement of
user code.  Basically, that API is practically useless except for the
narrow in-house use case for which it was created.  And yes, I already make
heavy use of the natural aggregate cache functionality but for the
limitation of only producing only one final output value.  A statement
level function cache obviously has more general use.

So, as I pointed out to Ben, the other poster who also discovered the
limitations of sqlite3_get_auxdata(), it would be trivial to get the
desired statement level cache speedup if only there were a per-connection
version of the API, say sqlite3_set_data(sqlite3*,void*) and void*
sqlite3_get_data(sqlite3*).

A connection level data area is definitely missing from the API.  To
implement one's own thread aware global connection to cache map wrapper
seems a lot to ask when it could so easily be added to the SQLite API.
Such an SQLite API implementation has a huge advantage in already having an
internal hash map implementation and total awareness of current operational
threading mode of SQLite.

That being said, there is still a documentation bug that is wasting
people's time.  A face value reading of the documentation at
https://www.sqlite.org/c3ref/get_auxdata.html gives the impression that
caching is solely dependent on the index and value of the function argument
when in fact there are 3 more very important and unstated limitations.
Documenting those limitations would only add one or two sentences but would
save people a lot of time.

Peter





On Mon, Nov 27, 2017 at 1:27 PM, Clemens Ladisch  wrote:

> petern wrote:
> > Evidently the sqlite3_get_auxdata() API is very buggy.
>
> The documentation says that
> | under some circumstances the associated metadata may be preserved.
>
> You have found circumstances where the metadata is not preserved.
>
> This is not a bug.
>
> I can see that you are unhappy, but that is just because you are trying
> to make it do something that it was never intended to do.
>
> If you want a function with mutable state over multiple invocations,
> do not use sqlite3_set_auxdata() but an aggregation function, or store
> the data elsewhere.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_get_auxdata() defect

[Clemens Ladisch]

petern wrote:
> Evidently the sqlite3_get_auxdata() API is very buggy.

The documentation says that
| under some circumstances the associated metadata may be preserved.

You have found circumstances where the metadata is not preserved.

This is not a bug.

I can see that you are unhappy, but that is just because you are trying
to make it do something that it was never intended to do.

If you want a function with mutable state over multiple invocations,
do not use sqlite3_set_auxdata() but an aggregation function, or store
the data elsewhere.


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

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[E.Pasma]

Op 27 nov 2017, om 20:51 heeft x het volgende geschreven:


So if I build a view that includes look-ups in other tables, the
optimizer may skip these at places where not selected. However only  
if

the look-ups are written as outer joins. Then it may be good practice
allways doing that. For instance:



create view vtrack as
select  trackname, artistname
from track
left join artist ON trackartist=artistid -- note left join

;
~
~

Yeah, I was thinking always to use left joins when it doesn’t make  
any difference. I’m using a C++ wrapper for SQLite I wrote myself.  
It contains a QueryGrid type that automatically splits the SQL into  
RowSQL and ColSQL as described earlier. It is still possible to  
build a vector of BaseTbl.RowIDs when the original query contains  
inner joins provided all columns of the unique index used in the  
join are satisfied (see ***Example). If it’s unable to do that (due  
to 1 to many joins)  it computes ‘select count(*) from .’ and  
sets ColSQL to ‘select ColList from ... limit ?1, ?2’ so that it  
still fits in with the ‘fetch a range of data’ modus operandi and  
also allows a record counter and vertical scrollbar positioning.


***Example

select ColList
from BaseTbl
left join Tbl_1 on Tbl_1.X = BaseTbl.X
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
left join Tbl_3 on Tbl3.Z = BaseTbl.Z
where BaseTbl.Col=?

Provided Tbl_2 has unique index on Y the RowID list could be had from

Select BaseTbl.RowID
from BaseTbl
left join Tbl_1 on Tbl_1.X = BaseTbl.X
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
left join Tbl_3 on Tbl3.Z = BaseTbl.Z
where BaseTbl.Col=?

which the SQLite query optimiser would presumably reduce to

Select BaseTbl.RowID
from BaseTbl
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
where BaseTbl.Col=?

I’ve still to write code to check the query contains no joins that  
are ‘1 to many’. I don’t suppose there’s an easy way of determining  
this from explain or explain query plan? At the moment all i can  
think of is to scrape the index name from the explain query plan  
Detail column, check the number of variables involved (i.e. number  
of ? marks) and compare with the number of columns in the index.



What about changing the remaining inner join to left join

Select BaseTbl.RowID
from BaseTbl
left join Tbl_2 on Tbl2.Y = BaseTbl.Y
where BaseTbl.Col=?

and see if the SQLiter optimizer now leaves Tbl_2 out from the query  
plan.  It will only do that if it is not a 1-to-n join.
But that leaves you with the change that the join is 1-to-0 so to say.  
I understood that was a show stopper.


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

Re: [sqlite] sqlite3_get_auxdata() defect

[petern]

Clemens.

Here it that example again, but with the argument factored out so there is
no question about value or memory location of the cache argument target.
Remarkably, caching doesn't work at all when one does this!

sqlite> WITH arg(x) AS (SELECT 'x'), t(id) AS (VALUES(1),(2)) SELECT
id,auxint(x,id),auxint(x,1),auxint(x,null),auxint(x) FROM arg,t;
id  auxint(x,id)  auxint(x,1)  auxint(x,null)  auxint(x)
--    ---  --  --
1   1 1
2   2 1

Compare with the original example using literal value for the cached
argument:

sqlite> WITH t(id) AS (VALUES(1),(2)) SELECT
id,auxint('x',id),auxint('x',1),auxint('x',null),auxint('x') FROM t;
id  auxint('x',id)  auxint('x',1)  auxint('x',null)  auxint('x')
--  --  -    ---
1   1   1
2   3   2

I think the poster Ben, from last week, bumped into the above bug and was
seeking advice about why his function argument cache was being flushed for
every row.

Evidently the sqlite3_get_auxdata() API is very buggy.

Clemens, I'm a bit surprised you can't see or don't want to see the defects
here.  Very curious to read your reply.

For completeness, here is the testing extension auxint.c once again:
--
/*
Further to https://sqlite.org/c3ref/get_auxdata.html
UDF auxint(X,I) - stores I plus the function argument X's current metadata
INT and returns the new value.
UDF auxint(X) - returns the current value of the function meta-data INT
associated with X.
*/
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
static void reclaim(void*p) {if (p) sqlite3_free(p);}
static void auxint(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  int* pInt = sqlite3_get_auxdata(context, 0);
  if (argc>1 && sqlite3_value_type(argv[1]) != SQLITE_NULL){
if (!pInt) {pInt = sqlite3_malloc(sizeof(int)); *pInt=0;
sqlite3_set_auxdata(context, 0, pInt, reclaim);}
*pInt += sqlite3_value_int(argv[1]);
  }
  if (pInt) sqlite3_result_int(context,*pInt);
}
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_auxint_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  int rc = sqlite3_create_function(db, "auxint", 1, SQLITE_UTF8, 0, auxint,
0, 0);
  if (SQLITE_OK == rc) sqlite3_create_function(db, "auxint", 2,
SQLITE_UTF8, 0, auxint, 0, 0);
  return rc;
}
--

On Mon, Nov 27, 2017 at 1:15 AM, Clemens Ladisch  wrote:

> petern wrote:
> > So, at the very least, the documentation at
> > https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete
> because:
> >
> > 1. The identical function in a different column of the same SELECT has a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > 2. Functions with identical names and different arg count, yet sharing
> > common identical meta data argument at the specified argument index, use
> a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > Ideally, these problems should be fixed instead of simply being
> documented
> > away.  And, if they are fixed, SELECT columns can have an efficient,
> > thread-safe, and powerful new expressiveness for the idioms of running
> > sums, running products, running concatenations, synthetic id columns, and
> > so on.
>
> The auxdata is intended as a cache, so it must depend only on the
> argument value, not on any other state.
>
> If you want to do something different, store the data elsewhere.  And
> there is not much of a guarantee when or how often user-defined functions
> are called, so even with your desired changes to the auxdata mechanism,
> you would not get the desired result.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[x]

>So if I build a view that includes look-ups in other tables, the
>optimizer may skip these at places where not selected. However only if
>the look-ups are written as outer joins. Then it may be good practice
>allways doing that. For instance:

>create view vtrack as
>select  trackname, artistname
>from track
>left join artist ON trackartist=artistid -- note left join
;
~
~

Yeah, I was thinking always to use left joins when it doesn’t make any 
difference. I’m using a C++ wrapper for SQLite I wrote myself. It contains a 
QueryGrid type that automatically splits the SQL into RowSQL and ColSQL as 
described earlier. It is still possible to build a vector of BaseTbl.RowIDs 
when the original query contains inner joins provided all columns of the unique 
index used in the join are satisfied (see ***Example). If it’s unable to do 
that (due to 1 to many joins)  it computes ‘select count(*) from .’ and 
sets ColSQL to ‘select ColList from ... limit ?1, ?2’ so that it still fits in 
with the ‘fetch a range of data’ modus operandi and also allows a record 
counter and vertical scrollbar positioning.

***Example

select ColList
from BaseTbl
left join Tbl_1 on Tbl_1.X = BaseTbl.X
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
left join Tbl_3 on Tbl3.Z = BaseTbl.Z
where BaseTbl.Col=?

Provided Tbl_2 has unique index on Y the RowID list could be had from

Select BaseTbl.RowID
from BaseTbl
left join Tbl_1 on Tbl_1.X = BaseTbl.X
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
left join Tbl_3 on Tbl3.Z = BaseTbl.Z
where BaseTbl.Col=?

which the SQLite query optimiser would presumably reduce to

Select BaseTbl.RowID
from BaseTbl
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
where BaseTbl.Col=?

I’ve still to write code to check the query contains no joins that are ‘1 to 
many’. I don’t suppose there’s an easy way of determining this from explain or 
explain query plan? At the moment all i can think of is to scrape the index 
name from the explain query plan Detail column, check the number of variables 
involved (i.e. number of ? marks) and compare with the number of columns in the 
index.

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

Re: [sqlite] sqlite3_get_auxdata() defect

[petern]

>The auxdata is intended as a cache, so it must depend only on the
>argument value, not on any other state.

I'm not following.  Where is the argument different in my example.  The
zeroth argument is always 'x'.  Are you saying the argument has to be the
same memory location as well as the same value?

Take another look at the example.



On Mon, Nov 27, 2017 at 1:15 AM, Clemens Ladisch  wrote:

> petern wrote:
> > So, at the very least, the documentation at
> > https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete
> because:
> >
> > 1. The identical function in a different column of the same SELECT has a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > 2. Functions with identical names and different arg count, yet sharing
> > common identical meta data argument at the specified argument index, use
> a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > Ideally, these problems should be fixed instead of simply being
> documented
> > away.  And, if they are fixed, SELECT columns can have an efficient,
> > thread-safe, and powerful new expressiveness for the idioms of running
> > sums, running products, running concatenations, synthetic id columns, and
> > so on.
>
> The auxdata is intended as a cache, so it must depend only on the
> argument value, not on any other state.
>
> If you want to do something different, store the data elsewhere.  And
> there is not much of a guarantee when or how often user-defined functions
> are called, so even with your desired changes to the auxdata mechanism,
> you would not get the desired result.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Assert failure line 72132, function: valueFromExpr

[nomad]

On Mon Nov 27, 2017 at 10:53:50AM -0500, Richard Hipp wrote:
> On 11/27/17, Richard Hipp  wrote:
> 
> > I am unable to reproduce the problem.
> 
> Dan suggested that I needed to enable foreign keys, and that did
> indeed enable me to repro the problem.

Glad to hear that. I was scratching my head wondering how I was going
to come up with a better test case.

> > assertion "0" failed: file "sqlite3.c", line 72132, function:
> > valueFromExpr
> >
> 
> This should be fixed in the latest Pre-release Snapshot, uploaded
> moments ago.

I can confirm that the latest pre-release version no longer generates
the error for me.

Thanks for the quick response.

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

Re: [sqlite] Need SQLite datafiles to be locked on OS level(Windows)

[David Raymond]

You sir win the quote award for today.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Monday, November 27, 2017 12:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] Need SQLite datafiles to be locked on OS level(Windows)


It's inconceivable ...

(granted that the word may not mean what I think it means)

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

Re: [sqlite] Assert failure line 72132, function: valueFromExpr

[Richard Hipp]

On 11/27/17, no...@null.net  wrote:
>
> assertion "0" failed: file "sqlite3.c", line 72132, function:
> valueFromExpr
>

This should be fixed in the latest Pre-release Snapshot, uploaded moments ago.

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

Re: [sqlite] Need SQLite datafiles to be locked on OS level(Windows)

[Nelson, Erik - 2]

Simon Slavin wrote on  Monday, November 27, 2017 12:39 PM
> Subject: Re: [sqlite] Need SQLite datafiles to be locked on OS level(Windows)
> 
> On 27 Nov 2017, at 4:51pm, Joe Mistachkin  wrote:
> 
> > Judging from the description so far, I think that both SQLite and
> > System.Data.SQLite are working correctly.
> >
> > It is true that System.Data.SQLite has an internal retry mechanism
> > that will wait 150ms between retries.   The PrepareRetries property
> > for the SQLiteConnection object is the maximum number of retries.
> > By default, it is 3, which means the total wait should be around
> > 450ms by default.

> But the OP may also have installed another AV system.  And there are lots of
> those and one or more may think they should be making chances inside a
> SQLite database.

It's inconceivable to me that any non-malicious application (including AV 
system) would randomly change the contents of files it didn't own.  I usually 
find the source of these kinds of things to be errors in my own program.

(granted that the word may not mean what I think it means)

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need SQLite datafiles to be locked on OS level(Windows)

[Simon Slavin]

On 27 Nov 2017, at 4:51pm, Joe Mistachkin  wrote:

> Judging from the description so far, I think that both SQLite and
> System.Data.SQLite are working correctly.
> 
> It is true that System.Data.SQLite has an internal retry mechanism
> that will wait 150ms between retries.   The PrepareRetries property
> for the SQLiteConnection object is the maximum number of retries.
> By default, it is 3, which means the total wait should be around
> 450ms by default.

Good to have someone who actually understands System.Data.SQLite contribute.

> That being said, I have not personally seen any built-in Windows
> application or system service prevent SQLite from access a file
> for more than a fraction of a second.
> 
> Maybe things are different if the Windows AV subsystem actually
> finds something "bad" in the file?

It’s complicated.  Windows has its own AV system which scans files.  That one 
cannot cause "schema changed", since it does things to the file (change 
protection, move, rename, delete).

But the OP may also have installed another AV system.  And there are lots of 
those and one or more may think they should be making chances inside a SQLite 
database.

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

Re: [sqlite] Need SQLite datafiles to be locked on OS level(Windows)

[Joe Mistachkin]

Simon Slavin wrote:
>
> I'm hoping that Joe Mistachkin, or someone else who understands
> that library sees this, just to check that it works the way
> you're expecting it to. 
>

Judging from the description so far, I think that both SQLite and
System.Data.SQLite are working correctly.

It is true that System.Data.SQLite has an internal retry mechanism
that will wait 150ms between retries.   The PrepareRetries property
for the SQLiteConnection object is the maximum number of retries.
By default, it is 3, which means the total wait should be around
450ms by default.

That being said, I have not personally seen any built-in Windows
application or system service prevent SQLite from access a file
for more than a fraction of a second.

Maybe things are different if the Windows AV subsystem actually
finds something "bad" in the file?

--
Joe Mistachkin


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

Re: [sqlite] Assert failure line 72132, function: valueFromExpr

[Richard Hipp]

On 11/27/17, Richard Hipp  wrote:

> I am unable to reproduce the problem.

Dan suggested that I needed to enable foreign keys, and that did
indeed enable me to repro the problem.

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

Re: [sqlite] Assert failure line 72132, function: valueFromExpr

[Richard Hipp]

On 11/27/17, no...@null.net  wrote:
> [version sqlite-snapshot-201711181730]
>
> I am seeing an error when attempting to insert a row with
> SQLITE_DEBUG enabled:
>
> assertion "0" failed: file "sqlite3.c", line 72132, function:
> valueFromExpr
>

I am unable to reproduce the problem.  The database and INSERT
statement you sent via private email work fine for me.  No errors or
warnings.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need SQLite datafiles to be locked on OS level (Windows)

[Simon Slavin]

On 27 Nov 2017, at 12:41pm, Jiří Matějka  wrote:

> We have no busy timeout set because we use System.Data.SQlite library which 
> has its own waiting mechanism (repeating the sqlite3_reset/sqlite3_step 
> within 30 secons).

I’m hoping that Joe Mistachkin, or someone else who understands that library 
sees this, just to check that it works the way you’re expecting it to.

> b) "database disk image is malformed", which is sometimes really caused by AV 
> software. Our application is an email client, so the antivirus could 
> theoretically found a virus signature inside datafile (but why would it just 
> modify the file instead of moving to the quarantine as whole?).
> We also have many reports with "database schema has changed" errors.

There are millions of installations on Wintel computers which have AV software 
installed.  We do not have millions of complaints about this problem.  This 
suggests that your setup is unusual.  As you write, AV software is expected to 
handle a SQLite database file as a whole: it could delete it, put protection on 
it, or move it.  Since AV software doesn’t understand the format of SQLite 
databases it should not be modifying the contents of the file leading to 
"schema has changed" but not "database corrupt".

Can you investigate further ?  Or tell us more about what you already know ?

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

Re: [sqlite] Assert failure line 72132, function: valueFromExpr

[Richard Hipp]

Please send a test case.  You can send it directly to me at
d...@sqlite.org if you like.

On 11/27/17, no...@null.net  wrote:
> [version sqlite-snapshot-201711181730]
>
> I am seeing an error when attempting to insert a row with
> SQLITE_DEBUG enabled:
>
> assertion "0" failed: file "sqlite3.c", line 72132, function:
> valueFromExpr
>
> Running "PRAGMA vdbe_addoptrace=ON" immediately before the insert
> produces the following
>
>1 Expire   000   00
>2 Halt 000   00
>0 Init 010   00 Start at 1
>1 OpenWrite040   00 root=4 iDb=0
>2 NewRowid 010   00 r[1]=rowid
>3 Variable 120   00 r[2]=parameter(1,)
>4 Variable 230   00 r[3]=parameter(2,)
>5 Variable 340   00 r[4]=parameter(3,)
>6 Variable 450   00 r[5]=parameter(4,)
>7 Variable 560   00 r[6]=parameter(5,)
>8 Variable 670   00 r[7]=parameter(6,)
>9 Variable 790   00 r[9]=parameter(7,)
>   10 Variable 8   100   00 r[10]=parameter(8,)
>   11 Variable 9   110   00 r[11]=parameter(9,)
>   12 HaltIfNull129922   00 if r[2]=null halt
>   13 HaltIfNull129923   00 if r[3]=null halt
>   14 HaltIfNull129924   00 if r[4]=null halt
>   15 HaltIfNull129925   00 if r[5]=null halt
>   16 HaltIfNull129926   00 if r[6]=null halt
>   17 HaltIfNull129927   00 if r[7]=null halt
>   18 HaltIfNull129929   00 if r[9]=null halt
>   19 HaltIfNull12992   10   00 if r[10]=null halt
>   20 MakeRecord   2   10   12   00 r[12]=mkrec(r[2..11])
>   21 Insert   0   121   00 intkey=r[1] data=r[12]
>0 Init 010   00 Start at 1
> CLEAR
> PUSH to 1
>1 Param   1620   00
>2 Param   2130   00
>3 Param   1240   00
>4 Param   1350   00
>5 Param   1460   00
>6 Param   1570   00
>7 Param   1780   00
>8 Param   1990   00
>9 Param   20   100   00
> POP  to 0
>   10 Function0  51121   00 r[1]=func(r[2])
> CLEAR
>   11 OpenWrite0  1110   00 root=111 iDb=0
>   12 OpenWrite1  1150   00 root=115 iDb=0
>   13 Param   16   110   00
>   14 NotNull 1100   00 if r[11]!=NULL goto 0
>   15 NewRowid 0   110   00 r[11]=rowid
>   16 MustBeInt   1100   00
>   17 SoftNull1200   00 r[12]=NULL
>   18 String8  0   130   00 r[13]=''
>   19 Param   21   140   00
>   20 Param   18   150   00
>   21 NotNull 15   -40   00 if r[15]!=NULL goto -4
> PUSH to 1
> PUSH to 2
>   22 String8  0   340   00 r[34]=''
>   23 String8  0   350   00 r[35]=''
> POP  to 1
>   24 Function03   34   15   00 r[15]=func(r[34])
> POP  to 0
>   25 Param   19   160   00
>   26 Param   20   170   00
> PUSH to 1
>   27 String8  0   360   00 r[36]=''
>   28 Param   20   400   00
>   29 Integer   1000   410   00 r[41]=1000
>   30 Divide  41   40   39   00 r[39]=r[40]/r[41]
>   31 NotNull 39   -50   00 if r[39]!=NULL goto -5
> PUSH to 2
> PUSH to 3
>   32 String8  0   420   00 r[42]=''
>   33 String8  0   430   00 r[43]=''
>   34 String8  0   440   00 r[44]=''
> POP  to 2
>   35 Function07   42   41   00 r[41]=func(r[42])
> PUSH to 3
>   36 String8  0   450   00 r[45]=''
>   37 String8  0   460   00 r[46]=''
> POP  to 2
>   38 Function03   45   40   00 r[40]=func(r[45])
>   39 Subtract40   41   39   00 r[39]=r[41]-r[40]
> POP  to 1
>   40 Integer   3600   400   00 r[40]=3600
>   41 Divide  40   39   37   00 r[37]=r[39]/r[40]
>   42 Cast37   680   00 affinity(r[37])
> PUSH to 2
>   43 Param   20   410

[sqlite] Assert failure line 72132, function: valueFromExpr

[nomad]

[version sqlite-snapshot-201711181730]

I am seeing an error when attempting to insert a row with
SQLITE_DEBUG enabled:

assertion "0" failed: file "sqlite3.c", line 72132, function: valueFromExpr

Running "PRAGMA vdbe_addoptrace=ON" immediately before the insert
produces the following 

   1 Expire   000   00 
   2 Halt 000   00 
   0 Init 010   00 Start at 1
   1 OpenWrite040   00 root=4 iDb=0
   2 NewRowid 010   00 r[1]=rowid
   3 Variable 120   00 r[2]=parameter(1,)
   4 Variable 230   00 r[3]=parameter(2,)
   5 Variable 340   00 r[4]=parameter(3,)
   6 Variable 450   00 r[5]=parameter(4,)
   7 Variable 560   00 r[6]=parameter(5,)
   8 Variable 670   00 r[7]=parameter(6,)
   9 Variable 790   00 r[9]=parameter(7,)
  10 Variable 8   100   00 r[10]=parameter(8,)
  11 Variable 9   110   00 r[11]=parameter(9,)
  12 HaltIfNull129922   00 if r[2]=null halt
  13 HaltIfNull129923   00 if r[3]=null halt
  14 HaltIfNull129924   00 if r[4]=null halt
  15 HaltIfNull129925   00 if r[5]=null halt
  16 HaltIfNull129926   00 if r[6]=null halt
  17 HaltIfNull129927   00 if r[7]=null halt
  18 HaltIfNull129929   00 if r[9]=null halt
  19 HaltIfNull12992   10   00 if r[10]=null halt
  20 MakeRecord   2   10   12   00 r[12]=mkrec(r[2..11])
  21 Insert   0   121   00 intkey=r[1] data=r[12]
   0 Init 010   00 Start at 1
CLEAR
PUSH to 1
   1 Param   1620   00 
   2 Param   2130   00 
   3 Param   1240   00 
   4 Param   1350   00 
   5 Param   1460   00 
   6 Param   1570   00 
   7 Param   1780   00 
   8 Param   1990   00 
   9 Param   20   100   00 
POP  to 0
  10 Function0  51121   00 r[1]=func(r[2])
CLEAR
  11 OpenWrite0  1110   00 root=111 iDb=0
  12 OpenWrite1  1150   00 root=115 iDb=0
  13 Param   16   110   00 
  14 NotNull 1100   00 if r[11]!=NULL goto 0
  15 NewRowid 0   110   00 r[11]=rowid
  16 MustBeInt   1100   00 
  17 SoftNull1200   00 r[12]=NULL
  18 String8  0   130   00 r[13]=''
  19 Param   21   140   00 
  20 Param   18   150   00 
  21 NotNull 15   -40   00 if r[15]!=NULL goto -4
PUSH to 1
PUSH to 2
  22 String8  0   340   00 r[34]=''
  23 String8  0   350   00 r[35]=''
POP  to 1
  24 Function03   34   15   00 r[15]=func(r[34])
POP  to 0
  25 Param   19   160   00 
  26 Param   20   170   00 
PUSH to 1
  27 String8  0   360   00 r[36]=''
  28 Param   20   400   00 
  29 Integer   1000   410   00 r[41]=1000
  30 Divide  41   40   39   00 r[39]=r[40]/r[41]
  31 NotNull 39   -50   00 if r[39]!=NULL goto -5
PUSH to 2
PUSH to 3
  32 String8  0   420   00 r[42]=''
  33 String8  0   430   00 r[43]=''
  34 String8  0   440   00 r[44]=''
POP  to 2
  35 Function07   42   41   00 r[41]=func(r[42])
PUSH to 3
  36 String8  0   450   00 r[45]=''
  37 String8  0   460   00 r[46]=''
POP  to 2
  38 Function03   45   40   00 r[40]=func(r[45])
  39 Subtract40   41   39   00 r[39]=r[41]-r[40]
POP  to 1
  40 Integer   3600   400   00 r[40]=3600
  41 Divide  40   39   37   00 r[37]=r[39]/r[40]
  42 Cast37   680   00 affinity(r[37])
PUSH to 2
  43 Param   20   410   00 
  44 Integer   1000   480   00 r[48]=1000
  45 Divide  48   41   47   00 r[47]=r[41]/r[48]
  46 NotNull 47   -60   00 if r[47]!=NULL goto -6
PUSH to 3
PUSH to 4
  47 String8  0   490   00 r[49]=''
  48 

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[E.Pasma]

So if I build a view that includes look-ups in other tables, the  
optimizer may skip these at places where not selected. However only if  
the look-ups are written as outer joins. Then it may be good practice  
allways doing that. For instance:


create view vtrack as
select  trackname, artistname
from track
left join artist ON trackartist=artistid -- note left join
;
~
~

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

Re: [sqlite] Need SQLite datafiles to be locked on OS level (Windows)

[Jiří Matějka]

Thanks for your responses.
Actually we have two problems:

a) "database is locked" which in majority comes from sqlite3_step doing 
BEGIN IMMEDIATE which indeed is obtaining a write lock
Basically we have just one connection per one datafile except one case 
when we have two connections for one datafile (one is read/write and the 
other is read only).
Now I see that all "database is locked" problems comes from these shared 
datafile connections. So maybe this problem is actually somewhere inside 
our application.


We have WAL but I don't understand how WAL introduces opportunity for 
"database is locked" errors.


We have no busy timeout set because we use System.Data.SQlite library 
which has its own waiting mechanism (repeating the 
sqlite3_reset/sqlite3_step within 30 secons).


b) "database disk image is malformed", which is sometimes really caused 
by AV software. Our application is an email client, so the antivirus 
could theoretically found a virus signature inside datafile (but why 
would it just modify the file instead of moving to the quarantine as 
whole?).

We also have many reports with "database schema has changed" errors.


I have implemented locking_mode=exclusive (along with shared cache) and 
will check if this makes difference.


Best regards,
Jiri

On 24.11.2017 20:08:34, "Keith Medcalf"  wrote:



No.

The Windows Search Indexer or Windows Defender only processes files 
when they are "closed" (that is, the act of closing a file adds it to 
the queue of files to be processed). As long as they are "open" they 
are not enqueued for processing. There are a few exceptions, but they 
all require "application support" to behave in the non-default "do not 
fiddle-faddle with open files".


To do otherwise would be completely foolish (or more correctly, 
brain-dead).


Of course, if the software in question is a third-party thing such as 
from Slimeytech, McCrappy, or another brain dead vendor, then it is 
possible that it fiddle-faddles with files that are "in use" because it 
requires a certain minimum (but very low) wattage to realize what a 
really truly bad idea (ie, completely and utterly brain-dead) that is.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven 
says a lot about anticipated traffic volume.




-Original Message-
From: sqlite-users [mailto:sqlite-users-
boun...@mailinglists.sqlite.org] On Behalf Of Nelson, Erik - 2
Sent: Friday, 24 November, 2017 10:20
To: SQLite mailing list
Subject: Re: [sqlite] Need SQLite datafiles to be locked on OS level
(Windows)

Jirí Matejka Sent on Friday, November 24, 2017 3:45 AM


but this does not prevent reading and modifying the file from other
processes while they are open by SQLite in our process.


Does antivirus or Windows randomly modify other applications' files?
It seems like no program could reliably execute under such
circumstances.


-
-
This message, and any attachments, is for the intended recipient(s)
only, may contain information that is privileged, confidential and/or
proprietary and subject to important terms and conditions available
at http://www.bankofamerica.com/emaildisclaimer. If you are not the
intended recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users




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

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

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[x]

>Thanks to you, this topic has inspired a useful change or changes.

Wow. Glad I could help.



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

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

Re: [sqlite] [EXTERNAL] Re: "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Hick Gunter]

Sorry to rain on your parade, but " The sqlite_sequence table does not track 
ROWID changes associated with UPDATE statement, only INSERT statements.", so if 
you change the (auto-generated) rowid, your "row age to row id" relation no 
longer holds. Same goes for updating the sqlite_sequence entry associated with 
the table.

Indeed, if you immediately update the sqlite_sequence entry for your INTEGER 
PRIMARY KEY to MAXINT64, you will achieve "random" rowids.


BTW: Consider the following transaction to implement LRU via rowids:

BEGIN
-- retrieve the rowid for the target row (store as rowid in calling program)
SELECT rowid,... FROM mytable WHERE ...;

-- retrieve the sequence value for the table (store as seq in calling program)
SELECT seq+1 FROM SQLITE_SEQUENCE WHERE name='mytable';

-- update values and set new rowid
UPDATE mytable SET rowid=?seq, ... WHERE rowid=?rowid;

-- update sequence value
UPDATE SQLITE_SEQUENCE SET seq=?seq WHERE name='mytable';

COMMIT;

Yes, this will probably cause foreign keys to break or require execssive work 
(ON UPDATE CASCADE).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Eduardo
Gesendet: Montag, 27. November 2017 10:28
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] "BIGINT" doesn't act like INTEGER on table 
creation [Bug]

On Thu, 23 Nov 2017 17:35:08 -0700
"Keith Medcalf"  escribió:

>
> That is correct.  You cannot spell "INTEGER PRIMARY KEY", which is an
> explicit alias for the rowid, as anything other than that one
> particular correct spelling.
>
> Personally, I have never found a use for the AUTOINCREMENT option.
> Why is it being used?

If you declare a INTEGER PRIMARY KEY alone, sqlite could reuse rowid number 
from deleted rows. Using AUTOINCREMENT forces sqlite to use a rowid bigger than 
the last one inserted/updated(1), so you can use it to know if one row is older 
or newer than others, without autoincrement no. You can do similar behavior 
with a trigger, for example for TEXT columns or automatically with DATE current 
time.

(1) You can change the integer primary key to whatever value you want.


> The fact that there's a Highway to Hell but only a Stairway to Heaven
> says a lot about anticipated traffic volume.

 See you there then? ;)


--
Eduardo 
___
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] Getting an advance list of RowIDs for a query result set

[E.Pasma]

x wrote:


From: E.Pasma
Sent: 26 November 2017 17:30
To: SQLite mailing list
Subject: Re: [sqlite] Getting an advance list of RowIDs for a query  
result set



If step 3 is xxx-ed and only left-joins remain to be considered then
the SQLite3 engine is likely to fullfill  this optimization in its
next release.
I tested this on the current  (2017-11-17) pre-release snapshot. As
far as I see any outer joined table may be discarded from the query
plan if only rowid from the base table is selected. Using SELECT
DISTINCT applies this also to one-to-many joins.


Thanks E. Pasma. Fixing the left join optimisation will be enough  
for me and will allow me to delete a fair amount of difficult code.  
My confusion over the inner joins arose out of trying to find a way  
of ensuring the BaseTbl RowIDs wouldn’t contain any duplicates.  
Somewhere along the line I forgot SQLite would have to still check  
the record existed in the inner joined table.



Thanks to you, this topic has inspired a useful change or changes.



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

Re: [sqlite] How to use sqlite3_table_column_metadata?

[Bart Smissaert]

> My advise to the VB guy would be to load the extension instead of trying
to
get VB to marshal pointers from the C API.

?? I do load the extension although it is compiled std_call, so VB6 can
talk to it.

RBS


On Mon, Nov 27, 2017 at 12:59 AM, petern 
wrote:

> I pasted that SQL into a SQLite shell and tried it out.
>
> First off,  Affinity (aka pragma table_info.type) column is case collated,
> so the LIKE operator should be used:
>
> select O.*
>   from SysColumns as O
>  where ObjectType == 'table'
>and IsPrimaryKey == 1
>and Affinity LIKE 'INTEGER'
>and not exists (select 1
>  from SysColumns as I
> where I.ObjectType == O.ObjectType
>   and I.ObjectName == O.ObjectName
>   and I.IsPrimaryKey > 1);
>
> [Side question. Do you find the '==' operator more readable/informative
> than the plain '=' SQL convention?]
>
> The main problem is still AUTOINCREMENT. You may not use AI, but a tool
> that reads others' schemas has to deal with that possibility.  As well, the
> system table sqlite_sequence will have no rows about an AI column when that
> AI table has no rows.  Therefore, the only general way, without calling the
> internal parser as I did in the extension, is to partly parse the SQL of
> the CREATE TABLE statement.
>
> Partial parsing isn't too hard I suppose.  There are a couple facts about
> the well formed CREATE statement that help.
>
> 1. The keywords PRIMARY KEY must be two words.
> 2. The keyword "AUTOINCREMENT" must come after "KEY" and before the next
> comma or closing bracket.
>
> From those facts it should be feasible to deduce the AUTOINCREMENT status
> by use of the instr() and substr() functions.
>
> Or, one could simply use the native -std=c90 style SQLite extension I
> posted for the other Peter...
>
> My advise to the VB guy would be to load the extension instead of trying to
> get VB to marshal pointers from the C API.  Though, I suppose that isn't
> the worst idea from recent posts.  Reading all the rows of a table in
> Python to get the count() has to be the best one.  In fact, that one is a
> classic job interview question.
>
>
>
>
>
>
> On Sun, Nov 26, 2017 at 1:41 PM, Keith Medcalf 
> wrote:
>
> > Not an answer to what is wrong with your call to get the metadata,
> > however, the information is all available from SQL.
> >
> > Data Catalog Views:
> >
> > drop view SysIndexColumns;
> > drop view SysIndexes;
> > drop view SysColumns;
> > drop view SysObjects;
> >
> > create view if not exists SysObjects
> > as
> > select type as ObjectType,
> >name as ObjectName
> >   from sqlite_master
> >  where type in ('table', 'view', 'index');
> >
> > create view if not exists SysColumns
> > as
> > select ObjectType,
> >ObjectName,
> >cidas ColumnID,
> >name   as ColumnName,
> >type   as Affinity,
> >"notnull"  as IsNotNull,
> >dflt_value as DefaultValue,
> >pk as IsPrimaryKey
> >   from SysObjects
> >   join pragma_table_info(ObjectName);
> >
> > create view if not exists SysIndexes
> > as
> > select ObjectType,
> >ObjectName,
> >name as IndexName,
> >seq  as IndexID,
> >"unique" as IsUniqueIndex,
> >origin   as IndexOrigin,
> >partial  as IsPartialIndex
> >   from SysObjects
> >   join pragma_index_list(ObjectName);
> >
> > create view if not exists SysIndexColumns
> > as
> > select ObjectType,
> >ObjectName,
> >IndexName,
> >seqno  as IndexColumnSequence,
> >cidas ColumnID,
> >name   as ColumnName,
> >"desc" as IsDescendingOrder,
> >coll   as Collation,
> >keyas IsPartOfKey
> >   from SysIndexes
> >   join pragma_index_xinfo(IndexName);
> >
> > Then
> >
> > select O.*
> >   from SysColumns as O
> >  where ObjectType == 'table'
> >and IsPrimaryKey == 1
> >and Affinity == 'integer'
> >and not exists (select 1
> >  from SysColumns as I
> > where I.ObjectType == O.ObjectType
> >   and I.ObjectName == O.ObjectName
> >   and I.IsPrimaryKey > 1);
> >
> > will return all the 'INTEGER PRIMARY KEY' in the "main" database ...
> >
> > I do not know what it does for "without rowid" tables where there is a
> > single field declared as "integer primary key", if you have some of those
> > you will have to figure it out (and how to eliminate them if you want to
> do
> > so) yourself.
> >
> > ---
> > The fact that there's a Highway to Hell but only a Stairway to Heaven
> says
> > a lot about anticipated traffic volume.
> >
> > >-Original Message-
> > >From: sqlite-users [mailto:sqlite-users-
> > >boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert
> > >Sent: Sunday, 26 November, 2017 13:14
> > >To: SQLite mailing list
> > >Subject: Re: 

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[x]

From: E.Pasma
Sent: 26 November 2017 17:30
To: SQLite mailing list
Subject: Re: [sqlite] Getting an advance list of RowIDs for a query result set

>If step 3 is xxx-ed and only left-joins remain to be considered then
>the SQLite3 engine is likely to fullfill  this optimization in its
>next release.
>I tested this on the current  (2017-11-17) pre-release snapshot. As
>far as I see any outer joined table may be discarded from the query
>plan if only rowid from the base table is selected. Using SELECT
>DISTINCT applies this also to one-to-many joins.

Thanks E. Pasma. Fixing the left join optimisation will be enough for me and 
will allow me to delete a fair amount of difficult code. My confusion over the 
inner joins arose out of trying to find a way of ensuring the BaseTbl RowIDs 
wouldn’t contain any duplicates. Somewhere along the line I forgot SQLite would 
have to still check the record existed in the inner joined table.

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

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

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

[Eduardo]

On Thu, 23 Nov 2017 17:35:08 -0700
"Keith Medcalf"  escribió:

> 
> That is correct.  You cannot spell "INTEGER PRIMARY KEY", which is an 
> explicit alias for the rowid, as anything other than that one particular 
> correct spelling.
> 
> Personally, I have never found a use for the AUTOINCREMENT option.  Why is 
> it being used?

If you declare a INTEGER PRIMARY KEY alone, sqlite could reuse rowid number
from deleted rows. Using AUTOINCREMENT forces sqlite to use a rowid bigger than
the last one inserted/updated(1), so you can use it to know if one row is older
or newer than others, without autoincrement no. You can do similar behavior
with a trigger, for example for TEXT columns or automatically with DATE current
time.

(1) You can change the integer primary key to whatever value you want.
 

> The fact that there's a Highway to Hell but only a Stairway to Heaven says a
> lot about anticipated traffic volume.

 See you there then? ;)
 

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

Re: [sqlite] sqlite3_get_auxdata() defect

[Clemens Ladisch]

petern wrote:
> So, at the very least, the documentation at
> https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete because:
>
> 1. The identical function in a different column of the same SELECT has a
> separate meta-data cache.

This is an implementation detail of the current version.

> 2. Functions with identical names and different arg count, yet sharing
> common identical meta data argument at the specified argument index, use a
> separate meta-data cache.

This is an implementation detail of the current version.

> Ideally, these problems should be fixed instead of simply being documented
> away.  And, if they are fixed, SELECT columns can have an efficient,
> thread-safe, and powerful new expressiveness for the idioms of running
> sums, running products, running concatenations, synthetic id columns, and
> so on.

The auxdata is intended as a cache, so it must depend only on the
argument value, not on any other state.

If you want to do something different, store the data elsewhere.  And
there is not much of a guarantee when or how often user-defined functions
are called, so even with your desired changes to the auxdata mechanism,
you would not get the desired result.


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