Re: [sqlite] New draft document on the new pointer-passing interfaces
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
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] 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
Re: [sqlite] New draft document on the new pointer-passing interfaces
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
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] New draft document on the new pointer-passing interfaces
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] rowid as foreign key
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
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] 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
Re: [sqlite] New draft document on the new pointer-passing interfaces
> 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
> 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
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
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
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
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
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] New draft document on the new pointer-passing interfaces
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
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
>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
Re: [sqlite] UPDATE database using parameters
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
Re: [sqlite] New draft document on the new pointer-passing interfaces
> 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
[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
Re: [sqlite] VALUES clause quirk or bug?
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
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
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
