Re: [sqlite] New draft document on the new pointer-passing interfaces

[Richard Hipp]

On 7/24/17, petern  wrote:
>
> Are sqlite3_result_subtype() and sqlite3_value_subtype() being deprecated
> in light of the duplicate functionality?
>

No.  The subtype() interfaces were originally created for completely
unrelated purposes (specifically to identify validated JSON text in
the JSON1 extension) and will continue to live on to serve those
unrelated purposes.

-- 
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] New draft document on the new pointer-passing interfaces

[Keith Medcalf]

Minor typo's and grammo's:

2.1) suppose -> supposed
  For example, the first argument to the snippet() function is suppose to be a 
special column
  space instead of the fts3cursor object it was suppose to be operating on

2.2) forge pointer -> forge a pointer
  forge pointer without the help of SQLite.

3)  forge a pointers -> forge pointers
  Hence, it is not possible for SQL to forge a pointers.

3.1) and -> an
  been passed and invalid pointer.

4)  operating -> operation
  insert a sort operating prior

4)  insert -> inserted
  pointer value insert into a query



---
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 Richard Hipp
>Sent: Monday, 24 July, 2017 05:54
>To: General Discussion of SQLite Database
>Subject: [sqlite] New draft document on the new pointer-passing
>interfaces
>
>https://www.sqlite.org/draft/bindptr.html
>
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Peter Da Silva]

What about imposing some structure on the pointer type strings that uses a 
guaranteed unique substring, for example “org.sqlite.fts3.snippet”,  to ensure 
there wouldn’t be accidental conflicts?

On 7/24/17, 6:54 AM, "sqlite-users on behalf of Richard Hipp" 
 
wrote:

https://www.sqlite.org/draft/bindptr.html

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


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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Dominique Devienne]

On Mon, Jul 24, 2017 at 1:54 PM, Richard Hipp  wrote:

> https://www.sqlite.org/draft/bindptr.html


Thanks. Very helpful. Still unsure whether not having a destructor D for
pointer P is a good thing though.
The text explicitly says the pointer is "destroyed" when not flowing
directly from producer to consumer,
which could mean calling the D(P) in that case. The current semantic seem
to imply the pointer producer
is longer lived than the pointer consumer, and thus no lifetime management
is necessary, but will that always be the case?

Also, the new pointer APIs are said to be an enhanced version of
*_subtype(), so there's some overlap,
but I guess one can use both at the same time, no? Or is _subtype()
deprecated? JSON1 uses _subtype
I believe, so _subtype() has more uses than the one _pointer() APIs replace
it for? Maybe an extra paragraph
regarding _pointer vs _subtype() would be helpful? At least I'd like your
perspective on it.

Thanks, --DD

Minor fixes:
s/Upping The Thread Level/Upping The Threat Level/
s/Consideration was giving to using integer values as the pointer
type/Consideration was giveb to using integer values as the pointer type/
s/if a pointer value insert into a query/if a pointer value inserted into a
query/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Hick Gunter]

Naming conventions (if strictly adhered to) are moderately good at avoiding 
conflicts, but take the guesswork out of "faking a pointer". It also assumes 
that pointers for "whatever" are interchangeable between different queries in a 
process, i.e. passing a "whatever" pointer from statement A to a function in 
statement B does not pose a risk.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peter Da Silva
Gesendet: Montag, 24. Juli 2017 15:37
An: SQLite mailing list 
Betreff: Re: [sqlite] New draft document on the new pointer-passing interfaces

What about imposing some structure on the pointer type strings that uses a 
guaranteed unique substring, for example “org.sqlite.fts3.snippet”,  to ensure 
there wouldn’t be accidental conflicts?

On 7/24/17, 6:54 AM, "sqlite-users on behalf of Richard Hipp" 
 
wrote:

https://www.sqlite.org/draft/bindptr.html

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


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


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

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


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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Peter Da Silva]

If you’re concerned about people faking pointers, you could always go with 
something like “org.sqlite.fts3.01Yzg5x.snippet”.

But given this design, you need to have already injected a C extension to even 
access the pointers, so I’m not sure what the issue is.

On 7/24/17, 9:19 AM, "sqlite-users on behalf of Hick Gunter" 
 
wrote:

Naming conventions (if strictly adhered to) are moderately good at avoiding 
conflicts, but take the guesswork out of "faking a pointer". It also assumes 
that pointers for "whatever" are interchangeable between different queries in a 
process, i.e. passing a "whatever" pointer from statement A to a function in 
statement B does not pose a risk.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peter Da Silva
Gesendet: Montag, 24. Juli 2017 15:37
An: SQLite mailing list 
Betreff: Re: [sqlite] New draft document on the new pointer-passing 
interfaces

What about imposing some structure on the pointer type strings that uses a 
guaranteed unique substring, for example “org.sqlite.fts3.snippet”,  to ensure 
there wouldn’t be accidental conflicts?

On 7/24/17, 6:54 AM, "sqlite-users on behalf of Richard Hipp" 
 
wrote:

https://www.sqlite.org/draft/bindptr.html

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


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


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

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


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


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

Re: [sqlite] rowid as foreign key

[Gwendal Roué]

> Le 24 juil. 2017 à 20:58, Roman Fleysher  a 
> écrit :
> 
> Dear SQLiters,
> 
> Is it possible to link two tables using rowid, the implicit column? I tried 
> and it did not work, so I presume the answer to my question is "no".

Hello Roman,

The answer is yes. For example :

$ sqlite3 /tmp/db.sqlite
sqlite> CREATE TABLE foo (c);
sqlite> CREATE TABLE bar (c);
sqlite> INSERT INTO foo (c) VALUES ('foo');
sqlite> INSERT INTO bar (c) VALUES ('bar');
sqlite> SELECT foo.rowid, foo.c, bar.rowid, bar.c FROM foo, bar WHERE foo.rowid 
= bar.rowid;
1|foo|1|bar

Gwendal Roué

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

Re: [sqlite] rowid as foreign key

[Roman Fleysher]

I am sorry, I did not ask the question correctly. I omitted "... link two 
tables, using foreign key...". 

I now see last sentence on http://sqlite.org/lang_createtable.html which states 
that it is not possible.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Gwendal Roué [gwendal.r...@gmail.com]
Sent: Monday, July 24, 2017 3:17 PM
To: SQLite mailing list
Subject: Re: [sqlite] rowid as foreign key

> Le 24 juil. 2017 à 20:58, Roman Fleysher  a 
> écrit :
>
> Dear SQLiters,
>
> Is it possible to link two tables using rowid, the implicit column? I tried 
> and it did not work, so I presume the answer to my question is "no".

Hello Roman,

The answer is yes. For example :

$ sqlite3 /tmp/db.sqlite
sqlite> CREATE TABLE foo (c);
sqlite> CREATE TABLE bar (c);
sqlite> INSERT INTO foo (c) VALUES ('foo');
sqlite> INSERT INTO bar (c) VALUES ('bar');
sqlite> SELECT foo.rowid, foo.c, bar.rowid, bar.c FROM foo, bar WHERE foo.rowid 
= bar.rowid;
1|foo|1|bar

Gwendal Roué

___
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] rowid as foreign key

[Roman Fleysher]

Dear SQLiters,

Is it possible to link two tables using rowid, the implicit column? I tried and 
it did not work, so I presume the answer to my question is "no".

Thank you,

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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[petern]

Gwendal.

Yes.  You've missed something.  My application is working code not a
hypothetical feature request.  BLOB application object pointer lifetime
presently works precisely as I've described and without memory leak.  My
point (and Dominique's point) was that this proposal as it stands isn't a
drop in replacement for that BLOB'ed application object pointer protocol.

Your reply also suggests superficial understanding.  For example, regarding
the SQLITE_TRANSIENT BLOB disposition you mentioned.   That one is
generally not useful here unless the particular application object can be
completely materialized elsewhere in memory from shallow copy without loss
of fidelity or introduction of memory leak.  I'm not talking about copying
dumb strings.  These are highly stateful and possibly third party objects
containing still more pointers and reference counters.  They cannot be
memcopied around willy-nilly.

Take a look at my earlier email that really applied the BLOB object
technique to functions if you have genuine interest.  This isn't some half
baked idea that came about in a flight of fancy.  There is a thread of
emails leading up to this point.





On Mon, Jul 24, 2017 at 11:12 AM, Gwendal Roué 
wrote:

>
> > Le 24 juil. 2017 à 19:02, petern  a écrit :
> >
> > Great.  But, if this is an ultimate replacement for BLOB'ed pointers,
> these
> > new pseudo-null pointers must support SQLITE_STATIC and destructor
> function
> > pointer lifetime disposition for those migrating their code.
>
> You're right that the new APIs make pointer values unsuitable for
> *intermediate* values.
>
> For example, `SELECT free(use(initialize(alloc(123`, while possible,
> is out of scope. One can define such functions, but it's very risky to
> misuse them, leak memory, or double-free.
>
> The new pointer values indeed look *designed* so that the  lifetime of
> pointed values is managed *outside* of any statement execution.
>
> I fail to see how blob'ed pointers did not have the exact same issue.
> SQLITE_STATIC and SQLITE_TRANSIENT manage the lifetime of the blob content,
> not of the content pointed by a blob'ed pointer. Or did I miss something?
>
> Gwendal Roué
>
> ___
> 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] New draft document on the new pointer-passing interfaces

[petern]

Your proposal does not walk through the alternative of sticking with
subtypes to add non-persistent sqlite3_bind_subtype() and corresponding
sqlite3_column_subtype() methods.  With a few extra lines and some
imagination can't this more straightforward alternative be combined with
the existing BLOB pointer interface to reach the desired outcome in FTS and
carray?

BTW, if the hypothetical attacker has a copy of the application, aren't the
constant space pointer access keys' string addresses all there in clear
text.  The castle walls will be no higher than those of a discretionary
pointer access protocol with subtypes.  In fact, subtypes could afford
greater security at runtime if the programmer rotates or otherwise
randomizes the type id's.






On Mon, Jul 24, 2017 at 10:05 AM, Richard Hipp  wrote:

> On 7/24/17, petern  wrote:
> >
> > Are sqlite3_result_subtype() and sqlite3_value_subtype() being deprecated
> > in light of the duplicate functionality?
> >
>
> No.  The subtype() interfaces were originally created for completely
> unrelated purposes (specifically to identify validated JSON text in
> the JSON1 extension) and will continue to live on to serve those
> unrelated purposes.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] rowid as foreign key

[Keith Medcalf]

Yes, but to use the rowid or a table in a foreign key declaration you need to 
specify it explicitly in the table definition declaring it as the INTEGER 
PRIMARY KEY.  (and exactly INTEGER PRIMARY KEY, although you can also add the 
AUTOINCREMENT keyword if you need to ensure unique values for some reason).

eg:  CREATE TABLE test (rowid INTEGER PRIMARY KEY, data text);

You need to do this because if you do not then the rowid is merely an internal 
identifier for the row in the underlying table b-tree structure and is not 
"stable" across vacuum or dump/load operations.

---
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 Roman Fleysher
>Sent: Monday, 24 July, 2017 12:58
>To: General Discussion of SQLite Database
>Subject: [sqlite] rowid as foreign key
>
>Dear SQLiters,
>
>Is it possible to link two tables using rowid, the implicit column? I
>tried and it did not work, so I presume the answer to my question is
>"no".
>
>Thank you,
>
>Roman
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Peter Da Silva]

On 7/24/17, 3:50 PM, "sqlite-users on behalf of petern" 
 wrote:
> BTW, if the hypothetical attacker has a copy of the application, aren't the 
> constant space pointer access keys' string addresses all there in clear text

But that’s not part of the security model, so what’s the problem?
 

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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Gwendal Roué]

> Le 24 juil. 2017 à 19:02, petern  a écrit :
> 
> To those posting low information congratulatory notes on this thread, you'd
> better hold off on popping those champagne corks.  The current API already
> contains irreversible additions to solve this problem that fell short.

Congrats can also go to clear documentation, scope, and rationale, even if the 
feature can be discussed :-)

Gwendal

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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[petern]

Great.  But, if this is an ultimate replacement for BLOB'ed pointers, these
new pseudo-null pointers must support SQLITE_STATIC and destructor function
pointer lifetime disposition for those migrating their code.

Why can't the producer destructor disposition be preserved within a chain
of application functions by subsequent consumers passing SQLITE_STATIC
disposition as they do now?   Isn't this feature just an accident of
statement scope controlled destruction that will continue to work with
tracked lifetime pseudo-null pointers?

BTW, let's call them what they are.  These are explicit pseudo-nulls for
the purpose of keeping pointer bits out of band from hacker SQL.

Also.

What is to stop black budget funded developers from creating popular
applications in the wild which preserve penetration channels of BLOB
pointers the original way?  Total security improvement justifications for
the pseudo-null pointer API are specious if the API is merely another
alternative.

Are sqlite3_result_subtype() and sqlite3_value_subtype() being deprecated
in light of the duplicate functionality?

Supplementing/deprecating the already secure sqlite3_X_subtype() API with a
more complete and pointer leak opaque replacement sqlite3_X_pointer() API
seems a worthy goal.  But, if that's the plan, where is the rest to the
proposal?  Honestly, it appears all you've proposed so far is yet another
way to pass pointers more aligned with the whims of your present tastes for
FTS3 MATCH, FTS5 extensions, and one code sample.

To those posting low information congratulatory notes on this thread, you'd
better hold off on popping those champagne corks.  The current API already
contains irreversible additions to solve this problem that fell short.


On Mon, Jul 24, 2017 at 4:54 AM, Richard Hipp  wrote:

> https://www.sqlite.org/draft/bindptr.html
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Gwendal Roué]

> Le 24 juil. 2017 à 19:02, petern  a écrit :
> 
> Great.  But, if this is an ultimate replacement for BLOB'ed pointers, these
> new pseudo-null pointers must support SQLITE_STATIC and destructor function
> pointer lifetime disposition for those migrating their code.

You're right that the new APIs make pointer values unsuitable for 
*intermediate* values.

For example, `SELECT free(use(initialize(alloc(123`, while possible, is out 
of scope. One can define such functions, but it's very risky to misuse them, 
leak memory, or double-free.

The new pointer values indeed look *designed* so that the  lifetime of pointed 
values is managed *outside* of any statement execution.

I fail to see how blob'ed pointers did not have the exact same issue. 
SQLITE_STATIC and SQLITE_TRANSIENT manage the lifetime of the blob content, not 
of the content pointed by a blob'ed pointer. Or did I miss something?

Gwendal Roué

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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[petern]

Justifications presented in the proposal claim hardwired constants for
mandatory lock and key style pointer value receiving are a great idea
because SQL can't generate constant space strings.  And, this is true
provided the executable is secret and remote.  I know there are a lot of
web server jockeys on this forum that are quick with the PHP answers so,
perhaps, this assertion makes sense to many.

What I am pointing out is how those same mandatory hardwired secret
constants work against security in the domain of local DB on a portable
device. On the local device the hacker attack space would be immediately
narrowed to constants listed in the executable which, I might add, are
guaranteed to work on remote copy of the same application!

As well, this particular justification apparently is the reason to make
something completely new and utterly parallel with the existing subtype
solution which works fine and could be extended to do the job.  Did you
read the part of the proposal essay where the existing API is mentioned?

Now, what about the first part of my reply?  No comment on that?  You
accept what I said there?  I'm glad to learn people are coming to their
senses. :-)



On Mon, Jul 24, 2017 at 1:52 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> On 7/24/17, 3:50 PM, "sqlite-users on behalf of petern" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> peter.nichvolo...@gmail.com> wrote:
> > BTW, if the hypothetical attacker has a copy of the application, aren't
> the constant space pointer access keys' string addresses all there in clear
> text
>
> But that’s not part of the security model, so what’s the problem?
>
>
> ___
> 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] rowid as foreign key

[Roman Fleysher]

Thank you, Keith. I realize this. I have a (parent) table that does not 
explicitly define primary key --- a unique identifier of the row. I need to 
temporarily create a child table that "extends" the row, adds columns to it. I 
wanted to use foreign keys on internal rowid column to link them. Perhaps I 
will use ALTER TABLE mechanism.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Keith Medcalf [kmedc...@dessus.com]
Sent: Monday, July 24, 2017 6:58 PM
To: SQLite mailing list
Subject: Re: [sqlite] rowid as foreign key

Yes, but to use the rowid or a table in a foreign key declaration you need to 
specify it explicitly in the table definition declaring it as the INTEGER 
PRIMARY KEY.  (and exactly INTEGER PRIMARY KEY, although you can also add the 
AUTOINCREMENT keyword if you need to ensure unique values for some reason).

eg:  CREATE TABLE test (rowid INTEGER PRIMARY KEY, data text);

You need to do this because if you do not then the rowid is merely an internal 
identifier for the row in the underlying table b-tree structure and is not 
"stable" across vacuum or dump/load operations.

---
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 Roman Fleysher
>Sent: Monday, 24 July, 2017 12:58
>To: General Discussion of SQLite Database
>Subject: [sqlite] rowid as foreign key
>
>Dear SQLiters,
>
>Is it possible to link two tables using rowid, the implicit column? I
>tried and it did not work, so I presume the answer to my question is
>"no".
>
>Thank you,
>
>Roman
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] VALUES clause quirk or bug?

[Hick Gunter]

You should NOT be relying on column names UNLESS you set them yourself.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von petern
Gesendet: Samstag, 08. Juli 2017 21:37
An: SQLite mailing list 
Betreff: [sqlite] VALUES clause quirk or bug?

Why does the choice of data value quotation mark influence the output column 
name of the inline VALUES clause? [This quirk was the origin of a recent bug in 
a current project.]

sqlite> .version
SQLite 3.19.3 2017-06-08 14:26:16
0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b

sqlite> .header on
sqlite> .mode csv
sqlite> SELECT * FROM (VALUES (1,2),(3,4));
"",:1
1,2
3,4
sqlite> SELECT * FROM (VALUES ("1",2),(3,4));
1,""
1,2
3,4
sqlite> SELECT * FROM (VALUES ('1',2),(3,4));
"",:1
1,2
3,4

I am aware that a less quirky "column" output column naming is available if 
the VALUES clause is evaluated directly.  See below.  However, this form is not 
applicable for task at hand, specifying inline constant tables within a query.

sqlite> VALUES ("1",2),(3,4);
column1,column2
1,2
3,4

Yes, I also thought carefully about the WITH clause.  See below.  While the 
WITH clause is natural for brief queries in a few columns, the wordiness an 
unnatural order is not helpful for local constant representation in the very 
lengthy queries needed for by this particular project.

sqlite> WITH Constants(c1,c2) AS (VALUES ("1",2),(3,4)) SELECT * FROM
Constants;
c1,c2
1,2
3,4

Is there anything I missed?  Are there other undocumented tricks of the VALUE 
clause that could help or hinder my quest?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

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


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

Re: [sqlite] Concurrent reads for VTs with in-memory data structures

[Hick Gunter]

We are using a shared memory segment (created during application startup) to 
contain the data records, but you could also use a memory mapped file. This 
will keep the static data identical across all connections and processes.

When writing your VT module, consider giving it a readonly/readwrite switch 
parameter. This can be used to provide the transaction and update methods (or 
not) in the method table within xCreate/xConnect. Not providing an xUpdate 
method tells SQLite that the table is strictly readonly and this may improve 
concurrency.

That way you can perform the inital load with SQL statements in the "bootstrap" 
program while the application itself can only read.

Bootstrap:

CREATE VIRTUAL TABLE my_table_rw USING my_module ( 'RW' );
INSERT INTO ...
DROP TABLE my_table_rw;


Applcation.

CREATE VIRTUAL TABLE my_table USING my_module ();

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dimitris Bil
Gesendet: Montag, 03. Juli 2017 19:14
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] Concurrent reads for VTs with in-memory data structures

I have some virtual tables that keep in-memory data structures. Data loading is 
happening at table creation (xCreate and xConnect are the same) and after that, 
during querying, only read access is needed. Queries do not access any other 
tables. Is there a way to achieve concurrent execution without having to keep 
multiple copies of each data structure?

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


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

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


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

Re: [sqlite] Doc bug. sqlite3_value_subtype() sqlite3_result_subtype() missing from obtaining/setting pages

[Darko Volaric]

There is no sqlite3_result_subtype() becuase there is no sqlite3_bind_subtype() 
becuase the subtype of a value cannot be stored in the database. That is my 
point. I don't know what you mean by your second paragraph, but subtype here is 
just an attribute of a value, similar to it's type (in SQLite each stored value 
can have its own type). Given SQLite is a database, it seems natural to be able 
to store a value's subtype along with the other attributes of a value, but for 
whatever reason the SQLite devs did not think that was nessessary.


> On Jul 24, 2017, at 7:57 AM, petern  wrote:
> 
> Users of a sqlite3_bind_subtype() wouldn't expect those bits to be
> persisted to disk considering that a function returned column value
> presented to INSERT or UPDATE doesn't have sqlite3_result_subtype() bits
> recorded.   Does that make a difference to your answer?
> 
> https://www.sqlite.org/c3ref/result_subtype.html
> 
> Subtypes as currently implemented are only useful for coordinating type
> safety among the application's transient objects.  I certainly wouldn't
> expect to read back hidden subtype bits from the disk to initialize/deduce
> a transient object's type.  In the interest of readability and transparency
> the design of the ordinarily visible part of table name and columns
> themselves would always fully encode the type of transient objects based on
> that table's rows.  Encoding stuff in persisted hidden column bits works
> against the "correct by inspection" objective of good design.
> 
> 
> 
> 
> 
> On Sun, Jul 23, 2017 at 9:13 PM, Darko Volaric  wrote:
> 
>> You can't have sqlite3_bind_subtype() because it would require a change to
>> the database file format - there would be no real performance penalty
>> besides increased record size. There is no existing place to store a
>> per-value subtype in the file format as it stands, although I did have a
>> design that would hide a few bits for the subtype at the end of the record
>> header where no-one is looking. It is a hack though and I gave up becuase
>> of having to also implement passing around the subtype internally, which
>> was then solved with the above functions, but by that time I had moved on
>> to a different solution. If you're really interested I can probably
>> describe the hack as I originally envisaged it.
>> 
>> Anyway, that's one theory, the other is that Richard isn't updating the
>> file format just to spite me for rudely arguing the need for subtypes a
>> couple of years ago. That or he just values the stability of the file
>> format.
>> 
>> 
>>> On Jul 24, 2017, at 2:40 AM, petern  wrote:
>>> 
>>> 2. These functions convey only the lower 8 bits of information and there
>>> there is no corresponding sqlite3_bind_subtype() but there is mention of
>>> expanding the number of bits in future SQLite versions. I take it the
>>> present 8 bits API can't be extended to a new sqlite3_bind_subtype()
>>> function for performance reasons?  Even 8 bits could hash a modest cache
>> of
>>> application pointers.  Just asking.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] New draft document on the new pointer-passing interfaces

[Gwendal Roué]

> Le 24 juil. 2017 à 13:54, Richard Hipp  a écrit :
> 
> https://www.sqlite.org/draft/bindptr.html

Thank you very much for this detailed rationale!

Gwendal Roué

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

Re: [sqlite] UPDATE database using parameters

[Peter Da Silva]

On 7/22/17, 1:46 AM, "sqlite-users on behalf of Keith Medcalf" 
 
wrote:
> Not very well.  How do you think "drive by downloads" work?  Javascript in 
> browsers is the most dangerous thing ever invented!

I think the caps-lock key gives it a run for its money.
 

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

[sqlite] New draft document on the new pointer-passing interfaces

[Richard Hipp]

https://www.sqlite.org/draft/bindptr.html

-- 
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] New draft document on the new pointer-passing interfaces

[Eric Minbiole]

>https://www.sqlite.org/draft/bindptr.html

Thank you very much for this, detailed, informative write-up, Dr Hipp. It's
very helpful to see the sensible rationale behind the new interfaces.
Thanks for continuing to enhance the API!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users