Re: [sqlite] sqlite-users Digest, Vol 126, Issue 28
Hmmm. SQLite version 3.25.0 2018-06-21 23:53:54 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .eqp on sqlite> create table t(id integer primary key not null, pid references t(id), body varchar); sqlite> create index t_pid on t (pid); sqlite> insert into t values (1,null,'body'); sqlite> insert or replace into t values (2,1,'body'); QUERY PLAN |--SCAN TABLE t USING COVERING INDEX t_pid `--SCAN TABLE t USING COVERING INDEX t_pid sqlite> insert or replace into t values (2,1,'body'); QUERY PLAN |--SCAN TABLE t USING COVERING INDEX t_pid `--SCAN TABLE t USING COVERING INDEX t_pid With or without correct affinity and even if you (most uselessly) specify NOT NULL on an INTEGER PRIMARY KEY ... Different versions of SQLite probably behave differently ... so what version are you using? --- 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 Allen >Sent: Thursday, 28 June, 2018 10:00 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] sqlite-users Digest, Vol 126, Issue 28 > >> If you give the parent column a proper affinity (ie, integer) do >you get "happiness making" results? > >nope, made no difference > > >> Date: Wed, 27 Jun 2018 17:47:01 -0600 >> From: "Keith Medcalf" >> To: "SQLite mailing list" >> Subject: Re: [sqlite] insert or replace performance with self >> "references"column >> Message-ID: <435b7882ff56764499aca977b8832...@mail.dessus.com> >> Content-Type: text/plain; charset="utf-8" >> >> >> If you give the parent column a proper affinity (ie, integer) do >you get "happiness making" results? >> >> --- >> 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 Allen >>>Sent: Wednesday, 27 June, 2018 16:20 >>>To: sqlite-users@mailinglists.sqlite.org >>>Subject: [sqlite] insert or replace performance with self >>>"references" column >>> >>>I have a table with an additional index and a query: >>> >>>"create table Transactions (Id integer primary key not null, Parent >>>references Transactions(id), Body varchar);" >>> >>>"create index Parent_Index on Transactions (Parent);" >>> >>>EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, >>>Body) values (?1, ?2, ?3); >>>23 0 0 SCAN TABLE Transactions >>>43 0 0 SCAN TABLE Transactions >>> >>>The double SCAN TABLE seems to have something to do with both the >>>"references" column and the "or replace" statement. If I remove >>>either, then the SCAN goes away. >>> >>>Questions: >>> >>>- Is my syntax for the " insert or replace" statement correct, and >>>will it do what I expect (insert a new row with a new auto- >generated >>>Id if Id is NULL, insert a new row with the given Id if Id is not >>>NULL >>>and no row exists with that Id, or update the existing row with the >>>given Id if Id is not NULL and a row exists with that Id)? >>> >>>- Is sqlite really doing one or two table scans to perform the >>>"insert >>>or replace" with a "references" self column? >>> >>>- If so, is there a way to eliminate this (other than removing >either >>>the "references" or the "or replace")? >>> >>>Thanks much. >___ >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] column types and constraints
"This flexible type-name arrangement works because SQLite is very forgiving about you putting non-proscribed values into columns - it tries to convert if it can do so without loss of information but if it cannot do a reversible type conversion it simply stores whatever you give it. Hence if you store a string '3456' into an INT column, it converts the string into an integer, but if you store a string 'xyzzy' in an INT column it will actually store the string value." This is an important feature of SQLite. In hindsight, an excellent decision. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
I understand that you do not want to break compatibility. But couldn't a PRAGMA STRICT_SQL (or the like) be introduced that would force to a) reject CREATE statements with unknown declarations (I often use "STRING" for the datatype, leading to hard-to-find problems as SQLite uses a numeric type for such a column) b) reject INSERT (or the like) statements with data that do not match the declaration - Original Message - From: Richard Hipp To: SQLite mailing list Sent: Thursday, June 28, 2018, 02:02:26 Subject: [sqlite] column types and constraints On 6/27/18, Mark Wagner wrote: > I recently pointed out that sqlite doesn't enforce type names and > constraints when creating tables but I was unable to explain/justify this > behavior. I'm sure this has come up before and there's a clear answer but > I didn't find it easily. > For example this is accepted without error: CREATE TABLE bar2 (x happy > days); In the early days of SQLite, the goal was to get it to parse the CREATE TABLE statements of as many different SQL engines as possible. I looked at the supported datatypes of contemporary engines, and they were all different. So to maximize compatibility, I made the decision to mostly ignore the "type" and accept any sequence of identifiers as the type. The actual type used it computed according to the following rules, in order: (1) If the type name contains "INT" then use INTEGER (2) If the type name contains "CHAR", "CLOB", or "TEXT" then use TEXT (3) If the type name contains "BLOB" then use BLOB (4) If the type name contains "REAL", "FLOA", or "DOUB" then use REAL (5) Otherwise use NUMERIC Those rules are defined here: https://www.sqlite.org/datatype3.html#affname This flexible type-name arrangement works because SQLite is very forgiving about you putting non-proscribed values into columns - it tries to convert if it can do so without loss of information but if it cannot do a reversible type conversion it simply stores whatever you give it. Hence if you store a string '3456' into an INT column, it converts the string into an integer, but if you store a string 'xyzzy' in an INT column it will actually store the string value. After the above decisions were made, SQLite became the most widely used database engine on the planet and over a trillion SQLite database files got created, and now we need to stick with that original idea lest we cause compatibility issues for all that legacy. -- 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] insert or replace performance with self "references" column
On 27 Jun 2018, at 11:20pm, Allen wrote: > I have a table with an additional index and a query: > > "create table Transactions (Id integer primary key not null, Parent > references Transactions(id), Body varchar);" > > "create index Parent_Index on Transactions (Parent);" > > EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, > Body) values (?1, ?2, ?3); > 23 0 0 SCAN TABLE Transactions > 43 0 0 SCAN TABLE Transactions SQLite version 3.22.0 2017-12-05 15:00:17 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> PRAGMA foreign_keys = ON; sqlite> CREATE TABLE Transactions (id INTEGER PRIMARY KEY, ...> parent REFERENCES Transactions(id), ...> Body TEXT); sqlite> create index Parent_Index on Transactions (Parent); sqlite> EXPLAIN QUERY PLAN insert into Transactions (Id, Parent, Body) values (3, 4, 'Hello'); sqlite> EXPLAIN QUERY PLAN insert OR REPLACE into Transactions (Id, Parent, Body) values (3, 4, 'Hello'); 0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index 0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index If you replace "not null' from your CREATE TABLE command, do you get the result I do ? What, specifically, INSERT OR REPLACE command did you use ? Which version of the command-line shell tool did you use ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 126, Issue 28
> If you give the parent column a proper affinity (ie, integer) do you get > "happiness making" results? nope, made no difference > Date: Wed, 27 Jun 2018 17:47:01 -0600 > From: "Keith Medcalf" > To: "SQLite mailing list" > Subject: Re: [sqlite] insert or replace performance with self > "references"column > Message-ID: <435b7882ff56764499aca977b8832...@mail.dessus.com> > Content-Type: text/plain; charset="utf-8" > > > If you give the parent column a proper affinity (ie, integer) do you get > "happiness making" results? > > --- > 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 Allen >>Sent: Wednesday, 27 June, 2018 16:20 >>To: sqlite-users@mailinglists.sqlite.org >>Subject: [sqlite] insert or replace performance with self >>"references" column >> >>I have a table with an additional index and a query: >> >>"create table Transactions (Id integer primary key not null, Parent >>references Transactions(id), Body varchar);" >> >>"create index Parent_Index on Transactions (Parent);" >> >>EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, >>Body) values (?1, ?2, ?3); >>23 0 0 SCAN TABLE Transactions >>43 0 0 SCAN TABLE Transactions >> >>The double SCAN TABLE seems to have something to do with both the >>"references" column and the "or replace" statement. If I remove >>either, then the SCAN goes away. >> >>Questions: >> >>- Is my syntax for the " insert or replace" statement correct, and >>will it do what I expect (insert a new row with a new auto-generated >>Id if Id is NULL, insert a new row with the given Id if Id is not >>NULL >>and no row exists with that Id, or update the existing row with the >>given Id if Id is not NULL and a row exists with that Id)? >> >>- Is sqlite really doing one or two table scans to perform the >>"insert >>or replace" with a "references" self column? >> >>- If so, is there a way to eliminate this (other than removing either >>the "references" or the "or replace")? >> >>Thanks much. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert or replace performance with self "references" column
Allen wrote: > create table Transactions (Id integer primary key not null, Parent references > Transactions(id), Body varchar); > create index Parent_Index on Transactions (Parent); > > EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, Body) > values (?1, ?2, ?3); > 23 0 0 SCAN TABLE Transactions > 43 0 0 SCAN TABLE Transactions > > - Is sqlite really doing one or two table scans to perform the "insert > or replace" with a "references" self column? It does the two scans for the foreign key constraint processing. (The rowid processing is not mentioned in the EQP output.) > - If so, is there a way to eliminate this (other than removing either > the "references" or the "or replace")? As mentioned by Keith, the index is not used because of the wrong affinity of the Parent column. With "Parent integer", both scans are efficient. Regars, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIMIT
Just wanted to support the idea of adding windowing functions to SQLite as it is the biggest hurdle for me to get others to use it. If size is an issue, I would love to have a build option to enable it. My $0.02 On Tue, Jun 26, 2018 at 8:27 AM Gert Van Assche wrote: > Thanks Olivier, very good to know. > > gert > > Op di 26 jun. 2018 om 09:06 schreef Olivier Mascia : > > > > Le 26 juin 2018 à 07:56, Gert Van Assche a écrit : > > > > > > If I would like to use these ranking techniques in SQLite, can I do > this > > > with an extension? Has nobody created a Windowing.dll yet? > > > Just asking. My short term need has been solved, but now that I know > > this, > > > I'm sure I'd like to use this technique later. But I like to stick to > > > SQLite. > > > > Don't want to spoil any news, it is viewable by the public anyway, but > > there are clearly experimental, interesting (and significant) work > ongoing > > by the SQLite developers around SQL windowing functions. A quick look to > > the exp-window-functions branch clearly shows that. > > > > https://www.sqlite.org/src/timeline?n=100=exp-window-functions > > > > So it _may_ be possible that you see these features in a later version of > > SQLite, or not. We'll see. I certainly wish the best for this > experience > > which looks good and nicely ongoing. > > > > From experience as a user of SQLite, not all development of features > > through branches get merged to the trunk. Sometimes they stay available > as > > a branch for you to choose to use, sometimes they end up in the main > SQLite > > product (trunk branch). Sometimes they can stay significant time aside > > before one day being merged. I have no specific expectations, and you > > shouldn't have too, I'm just monitoring areas of development that are > > interesting to my eyes and programming needs. Mainly the > > 'server-process-edition' branch, the 'begin-concurrent-pnu' branch and > this > > 'exp-window-functions' branch. > > > > :) > > > > -- > > Best Regards, Meilleures salutations, Met vriendelijke groeten, > > Olivier Mascia > > > > > > ___ > > 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] order by column_name collate custom_collation, with virtual table index
On Thu, Jun 28, 2018 at 2:03 PM Richard Hipp wrote: > On 6/28/18, Dominique Devienne wrote: > > From reading this list, I've learned that for an index to have a change > to > > be used to consume an order by, the collation of the query and the index > > must match. > > > > But in many instances, that index is one from a virtual table we > implement. > > So is there a way to tell SQLite that vindex is of a given custom > collation, > > to open the possibility of the index being used? > > The only way to avoid sorting the output of a virtual table is for the > xBestIndex routine to set the sqlite3_index_info.orderByConsumed boolean. > My point is more than when doing so, i.e. setting orderByConsumed to 1/true, there's no way that I can see for the vtable to know the collation used by the query, to validate whether it matches the vindex's own ordering. Or am I missing something? sqlite3_vtab_collation that Gunther pointed me to, works for aConstraint[] only, according to the doc, so there's no way to know the aOrderBy[] collation(s). --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shouldn't have to specify primary key explicitly
On 28 Jun 2018, at 12:48pm, Scott Robertson wrote:
> CREATE TABLE test2 (
> id INTEGER PRIMARY KEY,
> book text,
> page INTEGER
> );
>
> INSERT INTO test2 VALUES ('Lord of the Rings', 327);
>
> Error: table test2 has 3 columns but 2 values were supplied
>
> INSERT INTO test2 VALUES (9, 'Lord of the Rings', 327);
In the first example you declared a three-column table but supplied two values.
In the second example you explicitly stated that you wanted to supply values
for all the declared columns, so SQLite used the values you supplied.
To avoid this do either of the following:
INSERT INTO test2 VALUES (NULL, 'Lord of the Rings', 327);
INSERT INTO test2 (book, page) VALUES ('Lord of the Rings', 327);
Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shouldn't have to specify primary key explicitly
>INTEGER PRIMARY KEY doesn’t default to autoincrement. It’s used in place of the automatically created >autoincrement rowid but you have to supply the values (I.e. they’re not created automatically). I stand corrected. If you supply null for the integer primary key it will assign the highest rowid + 1. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shouldn't have to specify primary key explicitly
INTEGER PRIMARY KEY doesn’t default to autoincrement. It’s used in place of the automatically created autoincrement rowid but you have to supply the values (I.e. they’re not created automatically). On 28 Jun 2018, at 12:48, Scott Robertson wrote: > SQLite is supposed to autoincrement by default when a column is defined > as "INTEGER PRIMARY KEY" according to everything I've read. But I've > only gotten this to work if I let SQLite create its own PK column. If I > have an explicit PK column, I am expected to specify an ID myself. What > am I missing? I don't know why I'm getting this error. Thanks. You've defined the table with three cols so you have to provide three values unless you name the cols you wish to fill. To get SQLite to auto increment, use NULL as the value fo your id column: INSERT INTO test2 VALUES (NULL, 'Fletch', 245); -- Cheers -- Tim ___ 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] Shouldn't have to specify primary key explicitly
On 28 Jun 2018, at 12:48, Scott Robertson wrote: > SQLite is supposed to autoincrement by default when a column is defined > as "INTEGER PRIMARY KEY" according to everything I've read. But I've > only gotten this to work if I let SQLite create its own PK column. If I > have an explicit PK column, I am expected to specify an ID myself. What > am I missing? I don't know why I'm getting this error. Thanks. You've defined the table with three cols so you have to provide three values unless you name the cols you wish to fill. To get SQLite to auto increment, use NULL as the value fo your id column: INSERT INTO test2 VALUES (NULL, 'Fletch', 245); -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] order by column_name collate custom_collation, with virtual table index
On 6/28/18, Dominique Devienne wrote: > From reading this list, I've learned that for an index to have a change to > be used to consume an order by, the collation of the query and the index > must match. > > But in many instances, that index is one from a virtual table we implement. > So is there a way to tell SQLite that vindex is of a given custom collation, > to open the possibility of the index being used? The only way to avoid sorting the output of a virtual table is for the xBestIndex routine to set the sqlite3_index_info.orderByConsumed boolean. -- 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] [EXTERNAL] Shouldn't have to specify primary key explicitly
Supply a NULL for the INTEGER PRIMARY KEY to tell SQLite to "figure it out for
yourself" (c) Siddharta Gautama "Buddha"
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Scott Robertson
Gesendet: Donnerstag, 28. Juni 2018 13:48
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Shouldn't have to specify primary key explicitly
SQLite is supposed to autoincrement by default when a column is defined as
"INTEGER PRIMARY KEY" according to everything I've read. But I've only gotten
this to work if I let SQLite create its own PK column. If I have an explicit PK
column, I am expected to specify an ID myself. What am I missing? I don't know
why I'm getting this error. Thanks.
CREATE TABLE test1 (name TEXT, date DATE);
INSERT INTO test1 VALUES ('Barney', 1999
);
SELECT * FROM test1;
name date
-- --
Barney 1999
SELECT *, rowid FROM test1;
name date rowid
-- -- --
Barney 1999 1
CREATE TABLE test2 (
id INTEGER PRIMARY KEY,
book text,
page INTEGER
);
INSERT INTO test2 VALUES (
'Fletch',
245
);
Error: table test2 has 3 columns but 2 values were supplied
INSERT INTO test2 VALUES (
1,
'Dragnet',
17
);
SELECT *, rowid FROM test2;
id book page id
-- -- -- --
1 Dragnet 17 1
INSERT INTO test2 VALUES (
'Lord of the Rings',
327
);
Error: table test2 has 3 columns but 2 values were supplied
INSERT INTO test2 VALUES (
9,
'Lord of the Rings',
327
);
SELECT *, rowid FROM test2;
id book page id
-- -- -- --
1 Dragnet 17 1
9 Lord of th 327 9
--
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index
We are still using sqlite 3.7.14.1 here, so I can't verify what the current code does. Support for virtual tables has been much extended, adding (OTTOMH): - conflict resolution algorithm - support for unchanged columns in UPDATE statements - more detailed xBestIndex return values (# of estimated rows, UNIQUE flag) - collation sequence support VT implementations written before collation sequence support should be seen as supporting only BINARY. Since VT are by definition user defined, the same user has control over the queries. Changing the query to use a different collation sequence than the default BINARY requires changing the VT implementation to support that. Or at least check for BINARY and just not return that index number if a different sequence is required. I cannot check what current SQLite fills into the p_idx structure in the case of "SELECT * FROM vt ORDER BY field(s)". Adding fields from the ORDER BY but leaving the "usable" bit unset would solve the problem -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Donnerstag, 28. Juni 2018 12:56 An: General Discussion of SQLite Database Betreff: Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index On Thu, Jun 28, 2018 at 11:48 AM Dominique Devienne wrote: > > On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter wrote: >> >> The xBestIndex function needs to call the sqlite_vtab_collation() function to query the collation name required for each constraint and return the appropriate index number. >> >> Subs: yes, yes, see above > > > Oh, great! Thanks Gunther!!! > > Richard, may I suggest > https://www.sqlite.org/c3ref/vtab_collation.html to be mentioned or linked somewhere in https://www.sqlite.org/vtab.html? TIA. --DD Hmmm, on second thought, https://www.sqlite.org/c3ref/vtab_collation.html seems to work with constraints only (i.e. WHERE clause), and not the sqlite3_index_info.aOrderBy[]... The second argument must be an index into the aConstraint[] array belonging > to the sqlite3_index_info structure passed to xBestIndex So SQLite cannot reliably use a vindex to optimize an Order By if a custom collation is used in the query? Is that a latent bug? And apparently this returns the collation of the query, instead of providing the vindex's "own" collation. Which implies that it's the xBestIndex impl that supposed to rule out the vindex, not SQLite itself? In other words, any xBestIndex impl which does *NOT* call sqlite3_vtab_collation is necessarily buggy, if one day a query using a different collation (than the vindex) somehow uses a plan using that vindex? There seems to be a lot of subtle things to consider here. Can more light be shed on this? In the vtab.html doc? Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Shouldn't have to specify primary key explicitly
SQLite is supposed to autoincrement by default when a column is defined
as "INTEGER PRIMARY KEY" according to everything I've read. But I've
only gotten this to work if I let SQLite create its own PK column. If I
have an explicit PK column, I am expected to specify an ID myself. What
am I missing? I don't know why I'm getting this error. Thanks.
CREATE TABLE test1 (name TEXT, date DATE);
INSERT INTO test1 VALUES ('Barney', 1999
);
SELECT * FROM test1;
name date
-- --
Barney 1999
SELECT *, rowid FROM test1;
name date rowid
-- -- --
Barney 1999 1
CREATE TABLE test2 (
id INTEGER PRIMARY KEY,
book text,
page INTEGER
);
INSERT INTO test2 VALUES (
'Fletch',
245
);
Error: table test2 has 3 columns but 2 values were supplied
INSERT INTO test2 VALUES (
1,
'Dragnet',
17
);
SELECT *, rowid FROM test2;
id book page id
-- -- -- --
1 Dragnet 17 1
INSERT INTO test2 VALUES (
'Lord of the Rings',
327
);
Error: table test2 has 3 columns but 2 values were supplied
INSERT INTO test2 VALUES (
9,
'Lord of the Rings',
327
);
SELECT *, rowid FROM test2;
id book page id
-- -- -- --
1 Dragnet 17 1
9 Lord of th 327 9
--
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index
On Thu, Jun 28, 2018 at 11:48 AM Dominique Devienne wrote: > > On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter wrote: >> >> The xBestIndex function needs to call the sqlite_vtab_collation() function to query the collation name required for each constraint and return the appropriate index number. >> >> Subs: yes, yes, see above > > > Oh, great! Thanks Gunther!!! > > Richard, may I suggest https://www.sqlite.org/c3ref/vtab_collation.html to be mentioned or linked somewhere in https://www.sqlite.org/vtab.html? TIA. --DD Hmmm, on second thought, https://www.sqlite.org/c3ref/vtab_collation.html seems to work with constraints only (i.e. WHERE clause), and not the sqlite3_index_info.aOrderBy[]... The second argument must be an index into the aConstraint[] array belonging > to the sqlite3_index_info structure passed to xBestIndex So SQLite cannot reliably use a vindex to optimize an Order By if a custom collation is used in the query? Is that a latent bug? And apparently this returns the collation of the query, instead of providing the vindex's "own" collation. Which implies that it's the xBestIndex impl that supposed to rule out the vindex, not SQLite itself? In other words, any xBestIndex impl which does *NOT* call sqlite3_vtab_collation is necessarily buggy, if one day a query using a different collation (than the vindex) somehow uses a plan using that vindex? There seems to be a lot of subtle things to consider here. Can more light be shed on this? In the vtab.html doc? Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index
On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter wrote: > The xBestIndex function needs to call the sqlite_vtab_collation() function > to query the collation name required for each constraint and return the > appropriate index number. > > Subs: yes, yes, see above > Oh, great! Thanks Gunther!!! Richard, may I suggest https://www.sqlite.org/c3ref/vtab_collation.html to be mentioned or linked somewhere in https://www.sqlite.org/vtab.html? TIA. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index
The xBestIndex function needs to call the sqlite_vtab_collation() function to query the collation name required for each constraint and return the appropriate index number. Subs: yes, yes, see above -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Donnerstag, 28. Juni 2018 11:00 An: General Discussion of SQLite Database Betreff: [EXTERNAL] [sqlite] order by column_name collate custom_collation, with virtual table index From reading this list, I've learned that for an index to have a change to be used to consume an order by, the collation of the query and the index must match. But in many instances, that index is one from a virtual table we implement. So is there a way to tell SQLite that vindex is of a given custom collation, to open the possibility of the index being used? FWIW, the collation is a "natural order" one, i.e. a1, a2, ..., a10, ..., a19, a20, ..., a100. Right now the vindex is lexicographic, not "natural order", but of I can have SQLite use it somehow, I can easily change my vindex to "natural order" too. Subsidiary questions: Can one have two indexes on the same column with different collations? And thus have SQLite consider these alternate indexes depending on queries collations? Which goes back to my question about how telling SQLite about a vindex's collation. Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] order by column_name collate custom_collation, with virtual table index
On Thu, Jun 28, 2018 at 10:59 AM Dominique Devienne wrote: > So is there a way to tell SQLite that vindex is of a given custom > collation, > to open the possibility of the index being used? > Note that there's no mention at all of "collation" or "collate" in https://www.sqlite.org/vtab.html so this might once again be one of these corners of SQLite vtables where functionality in not on-par with "real" tables and indexes. I hope I'm wrong though, and hope to hear about it here. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] order by column_name collate custom_collation, with virtual table index
From reading this list, I've learned that for an index to have a change to be used to consume an order by, the collation of the query and the index must match. But in many instances, that index is one from a virtual table we implement. So is there a way to tell SQLite that vindex is of a given custom collation, to open the possibility of the index being used? FWIW, the collation is a "natural order" one, i.e. a1, a2, ..., a10, ..., a19, a20, ..., a100. Right now the vindex is lexicographic, not "natural order", but of I can have SQLite use it somehow, I can easily change my vindex to "natural order" too. Subsidiary questions: Can one have two indexes on the same column with different collations? And thus have SQLite consider these alternate indexes depending on queries collations? Which goes back to my question about how telling SQLite about a vindex's collation. Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 28 Jun 2018, at 5:43am, Igor Tandetnik wrote: > It's not mentioned here though: > https://sqlite.org/syntax/column-constraint.html The syntax diagrams in the SQLite documentation are ... what's the term ? ... sufficient but not exhaustive. In other words you can use some forms which violate the syntax diagrams without getting an error message. Normal warnings apply: your "illegal" form may be accepted by one version of SQLite, but a later form may reject it as a syntax error. Or worse still, it may accept it but interpret it differently. So try not to do that. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
