Re: [sqlite] [EXTERNAL] Re: Things you shouldn't assume when you store names
>> A growing number of organisations now ask me for my DOB or my >> postcode, rather than my name, when looking me up. I think you just >> explained why. In my country we have an increasing number of foreign >> family names, which probably helps it along. > >UK postcodes are incredibly fine-grained, compared to most of the rest of the >world, where they would be much less useful for identification. > >Eric Back when I lived in England our postcode was DT1 2DQ with the first substring indicating the postmans' route (Dorchester Town 1) and the second substring indicating the approximate position along the route i.e. the "visitation order" of addresses along that route. A simple sort by postcode made sure that the mail landed in the correct bag and that the postman needed to look only at the next letters' postcode to know where to go next. Postcodes here in Austria only designate the nearest delivery post office (which may change) ___ 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] Entity Framework Core support
Hi All, I can see System.Data.Sqlite supports Entity Framework 6. Are there any plans to support Entity Framework Core? Apologies if this is a basic question! Cheers, Mike ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding a record to a table with one value change
On 14 Nov 2019, at 10:27pm, Jake Thaw wrote: > Why not like this? > > insert into t (a, b, c, d, e, idate) > SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY > idate desc limit 1; Dammit. I thought I had tried this, and received a syntax error. Now I see that it was because I missed out a comma. Thanks for the correction. Good illustration of why responses should go to the list rather than direct to the OP. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adding a record to a table with one value change
On Thursday, 14 November, 2019 15:27, Jake Thaw wrote: >Why not like this? >insert into t (a, b, c, d, e, idate) >SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY >idate desc limit 1; Or, if using bound paramaters (and you should be): insert into t (a, b, c, d, e, idate) select ?, b, c, ?, e, ? from t where a = ?1 order by idate desc limit 1; then you bind the three parameters a, d, idate. Whether you want "order by idate desc" or "order by idate" depends on whether you want the newest or oldest record to be the template. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization
Unfortunately that is not reliable either because the aux_data is specific to the context and each invocation within the statement can have a different context. What does work is if you use the undocumented cross-context aux_data by using a negative argument number (note, since this is undocumented it is liable to change in future versions of SQLite3). Passing the cross-context aux_data location as an argument seems to work, eg., expensive_function(-1, rowid, ?99, vdata) where -1 is the aux_data location to use, rowid is used in the aux_data struct so you know if you can use the cached result, and ?99 and vdata are the parameters if calculation is required. -- 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 On >Behalf Of Keith Medcalf >Sent: Thursday, 14 November, 2019 11:16 >To: SQLite mailing list >Subject: Re: [sqlite] SQLITE_DETERMINISTIC and custom function >optimization > >>On Thursday, 14 November, 2019 03:52, Dominique Devienne > wrote: > >>>On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal wrote: > Thanks to all the friendly people who commented on my question. Much appreciated :-) > I was able to solve this with a small trick: I created a small 'state' struct with a rowid and the result (float) for that row. > >>> Sounds like you re-invented >>> https://www.sqlite.org/c3ref/get_auxdata.html >>> but with global state, no? >>> I replied to your original thread with that link, before seeing this >>> message. Using the built-in SQLite >>> mechanism for function caching is much better, because it's clean, and >>> properly handles the lifetime >>> of the cache, tying it to the statement execution lifetime. > >>get/set auxdata is apparently intended to cache auxillary data >associated >>with a CONSTANT provided to a function parameter, not the dynamic result >>of a computation. I suppose you could attempt to store your cache >>results against argument 2 (the table column), however that will >probably >>not achieve the effect desired since this is not the purpose of get/set >>auxdata (read the web page describing it). And storing it against >>argument 1 will not work because, although that is a constant, how would >>you ever know when the *value* of argument 2 changed? > >>Maybe I will write a wee test to see if it does work as you think or if >>it only works as documented. > >The function only works as documented in that auxdata set against non- >constant parameters is not maintained. This could be made to work with >"some random function" where one of the parameters is a constant or a >bound parameter by passing the cache determinant as a parameter and >caching the determinant and the result against the constant agrument. > >eg: > >expensive_function(?99, vdata, rowid) > >could have a structure containing a arg[2] and the result, and set this >as auxdata against argument 0 anytime it is computed. Before computing >one would check that the retrieved auxdata rowid == arg[2] and if so >simply return the result from the auxdata. This assumes that the value >of vdata is dependant on the value of rowid, of course. > >You can of course move the parameters around, but only auxdata stored >against a constant has a chance of being preserved. In this case the >constant is a bound value. Where there is no constant, you would have to >"create" one ... eg expensive_function(1, rowid, data1) so that you have >somewhere to store the auxdata ... > >-- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > > > >___ >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] Adding a record to a table with one value change
Why not like this? insert into t (a, b, c, d, e, idate) SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY idate desc limit 1; On Fri, Nov 15, 2019 at 9:19 AM Simon Slavin wrote: > > On 14 Nov 2019, at 10:06pm, Jose Isaias Cabrera wrote: > > > insert into t (a, b, c, d, e, idate) values > > ( > >(SELECT a FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > >(SELECT b FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > >(SELECT c FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > >'y', > >(SELECT e FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), > >'2019-02-12' > > ); > > > > Is there a simpler way? Thanks. > > No simpler way. I suggest you duplicate the exiting row first, then UPDATE > the duplicate. > ___ > 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] Adding a record to a table with one value change
On 14 Nov 2019, at 10:06pm, Jose Isaias Cabrera wrote: > insert into t (a, b, c, d, e, idate) values > ( >(SELECT a FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), >(SELECT b FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), >(SELECT c FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), >'y', >(SELECT e FROM t WHERE a = 'p001' ORDER BY idate desc limit 1), >'2019-02-12' > ); > > Is there a simpler way? Thanks. No simpler way. I suggest you duplicate the exiting row first, then UPDATE the duplicate. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Adding a record to a table with one value change
Greetings!
I have this table,
create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4,
'2019-02-11');
select * from t;
1|p001|1|2|n|4|2019-02-11
2|p002|2|2|n|4|2019-02-11
3|p003|3|2|n|4|2019-02-11
4|p004|4|2|y|4|2019-02-11
5|p005|5|2|y|4|2019-02-11
and I want to add a new record based on the p001 record. I only want to change
two values, d and idate. I can do this with this command,
insert into t (a, b, c, d, e, idate) values
(
(SELECT a FROM t WHERE a = 'p001' ORDER BY idate desc limit 1),
(SELECT b FROM t WHERE a = 'p001' ORDER BY idate desc limit 1),
(SELECT c FROM t WHERE a = 'p001' ORDER BY idate desc limit 1),
'y',
(SELECT e FROM t WHERE a = 'p001' ORDER BY idate desc limit 1),
'2019-02-12'
);
Is there a simpler way? Thanks.
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization
>On Thursday, 14 November, 2019 03:52, Dominique Devienne >wrote: >>On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal wrote: >>> Thanks to all the friendly people who commented on my question. Much >>> appreciated :-) >>> I was able to solve this with a small trick: >>> I created a small 'state' struct with a rowid and the result (float) >>> for that row. >> Sounds like you re-invented >> https://www.sqlite.org/c3ref/get_auxdata.html >> but with global state, no? >> I replied to your original thread with that link, before seeing this >> message. Using the built-in SQLite >> mechanism for function caching is much better, because it's clean, and >> properly handles the lifetime >> of the cache, tying it to the statement execution lifetime. >get/set auxdata is apparently intended to cache auxillary data associated >with a CONSTANT provided to a function parameter, not the dynamic result >of a computation. I suppose you could attempt to store your cache >results against argument 2 (the table column), however that will probably >not achieve the effect desired since this is not the purpose of get/set >auxdata (read the web page describing it). And storing it against >argument 1 will not work because, although that is a constant, how would >you ever know when the *value* of argument 2 changed? >Maybe I will write a wee test to see if it does work as you think or if >it only works as documented. The function only works as documented in that auxdata set against non-constant parameters is not maintained. This could be made to work with "some random function" where one of the parameters is a constant or a bound parameter by passing the cache determinant as a parameter and caching the determinant and the result against the constant agrument. eg: expensive_function(?99, vdata, rowid) could have a structure containing a arg[2] and the result, and set this as auxdata against argument 0 anytime it is computed. Before computing one would check that the retrieved auxdata rowid == arg[2] and if so simply return the result from the auxdata. This assumes that the value of vdata is dependant on the value of rowid, of course. You can of course move the parameters around, but only auxdata stored against a constant has a chance of being preserved. In this case the constant is a bound value. Where there is no constant, you would have to "create" one ... eg expensive_function(1, rowid, data1) so that you have somewhere to store the auxdata ... -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Things you shouldn't assume when you store names
On Wed, 13 Nov 2019 17:18:05 -0700 SQLite mailing list sqlite-users@mailinglists.sqlite.org said 8>< Give up on names and use something else? (SSN, phone number, DOB…) None of the above are safe primary keys. I don't think there is any single combination which is. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Things you shouldn't assume when you store names
On Thursday, 14 November, 2019 09:35, Eric wrote: >On Thu, 14 Nov 2019 00:24:09 + SQLite mailing list >sqlite-users@mailinglists.sqlite.org said >> A growing number of organisations now ask me for my DOB or my postcode, >> rather than my name, when looking me up. I think you just explained >> why. In my country we have an increasing number of foreign family names, >> which probably helps it along. >UK postcodes are incredibly fine-grained, compared to most of the rest of >the world, where they would be much less useful for identification. Bounding the search space to displaying a handful of names is better then guessing how someone else decided to spell something. It is quite obvious how that someone spelled "schmidt" as "chmit" when one is looking at a list of names associated with a given post code because you are now using a bag-of-mostly-water to intelligently choose from amongst a small list of candidates (a few hundred lets say) rather than the electronic precision of an index lookup. Similar results could be achieved "the old fashioned way" by presenting the user with a list of candidates matching the soundex, but this does not always work and may often not narrow down the selection list sufficiently to help the bag-of-mostly-water discern the name. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Things you shouldn't assume when you store names
On Wed, 13 Nov 2019 18:41:54 -0700 SQLite mailing list sqlite-users@mailinglists.sqlite.org said On Wednesday, 13 November, 2019 17:18, Warren Young wrote: 8>< ... Useless pricks having no need of a phone number usually get (911) 911-9111 ... It is totally out of order to dismiss _any_ minority by applying an abusive label to them. One of these days you may well be a minority in some context or other. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization
-- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. On Thursday, 14 November, 2019 03:52, Dominique Devienne wrote: >On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal wrote: >> Thanks to all the friendly people who commented on my question. Much >> appreciated :-) >> I was able to solve this with a small trick: >> I created a small 'state' struct with a rowid and the result (float) >> for that row. > Sounds like you re-invented https://www.sqlite.org/c3ref/get_auxdata.html > but with global state, no? > I replied to your original thread with that link, before seeing this > message. Using the built-in SQLite > mechanism for function caching is much better, because it's clean, and > properly handles the lifetime > of the cache, tying it to the statement execution lifetime. get/set auxdata is apparently intended to cache auxillary data associated with a CONSTANT provided to a function parameter, not the dynamic result of a computation. I suppose you could attempt to store your cache results against argument 2 (the table column), however that will probably not achieve the effect desired since this is not the purpose of get/set auxdata (read the web page describing it). And storing it against argument 1 will not work because, although that is a constant, how would you ever know when the *value* of argument 2 changed? Maybe I will write a wee test to see if it does work as you think or if it only works as documented. > Of course, if you want to tie your cache to a longer lifetime, *across* > statement executions, > you can use a global cache independent of SQLite, as it seems you did, > but > global state like > this is rarely a good idea in my experience :). YMMV. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Things you shouldn't assume when you store names
On Thu, 14 Nov 2019 00:24:09 + SQLite mailing list sqlite-users@mailinglists.sqlite.org said 8>< A growing number of organisations now ask me for my DOB or my postcode, rather than my name, when looking me up. I think you just explained why. In my country we have an increasing number of foreign family names, which probably helps it along. UK postcodes are incredibly fine-grained, compared to most of the rest of the world, where they would be much less useful for identification. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Specific sqlite_autoindex_* missing in recent SQLite versions
Apparently it got smarter about "primary key unique" in 3.20.0 and stopped making the extra index when it's a without rowid table. Don't see anything about it in the release notes though. Even on the current release "primary key unique" will still make an extra index for the unique if it's a rowid table. -Original Message- From: sqlite-users On Behalf Of Vincas Dargis Sent: Thursday, November 14, 2019 4:28 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Specific sqlite_autoindex_* missing in recent SQLite versions Hi list, Accidentally, when performing VACUUM using rather old SQLite 3.16.2 (from Debian 8 stretch) binary on some database file created with more recent SQLite (like 3.29.0 available in Qt 5.13.2 or a bit older), I've discovered that database now has a few more `sqlite_autodinex_*` entries in `sqlite_master` table (checked by test suite). I was puzzled at the beginning, but after some fiddling I see that creating this table: ``` CREATE TABLE "equipment_type"( id INTEGER NOT NULL PRIMARY KEY UNIQUE, name TEXT NOT NULL UNIQUE ) WITHOUT ROWID; ``` with recent SQLite, we get this set (only one) of internal indexes: ``` SELECT * from sqlite_master WHERE name LIKE 'sqlite_autoindex_eq%' index sqlite_autoindex_equipment_type_2 equipment_type 3 ``` Meanwhile, if I create same table using older 3.16.2 (on Debian 9 stretch), I get indexes for two fields: ``` index|sqlite_autoindex_equipment_type_1|equipment_type|3| index|sqlite_autoindex_equipment_type_2|equipment_type|4| ``` It feels strange if UNIQUE and PRIMARY KEY did not provoke creating `sqlite_autoindex`, but maybe we are missing something? Is this behavior change expected? Thanks! ___ 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] SQLITE_DETERMINISTIC and custom function optimization
On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal wrote: > Thanks to all the friendly people who commented on my question. Much > appreciated :-) > > I was able to solve this with a small trick: > I created a small 'state' struct with a rowid and the result (float) for > that row. > Sounds like you re-invented https://www.sqlite.org/c3ref/get_auxdata.html but with global state, no? I replied to your original thread with that link, before seeing this message. Using the built-in SQLite mechanism for function caching is much better, because it's clean, and properly handles the lifetime of the cache, tying it to the statement execution lifetime. Of course, if you want to tie your cache to a longer lifetime, *across* statement executions, you can use a global cache independent of SQLite, as it seems you did, but global state like this is rarely a good idea in my experience :). YMMV. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization
On Fri, Nov 8, 2019 at 9:20 PM Keith Medcalf wrote: > [...] The optimizer is prone to calculating things more often than it > needs to, and is difficult to force to "materialize" things. Since your expensive function needs to be calculated for every row of the > table anyway, it would be better to just create a table that has it calculated once, then compute the updates table, then perform > the update, then get rid of the extra tables. [...] > A better option IMHO is for the function itself to memoize its results, for the duration of the statement's execution. That way even if it's called multiple times, you can fetched the cached result instead of re-performing the expensive computation. Use https://www.sqlite.org/c3ref/get_auxdata.html for the caching. --DD PS: Didn't read the whole thread in detail, maybe my answer is a bit off topic :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Specific sqlite_autoindex_* missing in recent SQLite versions
Maybe you are confusing the autoindex logic by including superflous attributes: ... Id INTEGER NOT NULL PRIMARY KEY UNIQUE ... NOT NULL is enforced for WITHOUT ROWID tables and a single field PRIMARY KEY already implies UNIQUE, so no autoindex is required for Id This leaves only the autoindex required for the UNIQUE constraint on name. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Vincas Dargis Gesendet: Donnerstag, 14. November 2019 10:28 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Specific sqlite_autoindex_* missing in recent SQLite versions Hi list, Accidentally, when performing VACUUM using rather old SQLite 3.16.2 (from Debian 8 stretch) binary on some database file created with more recent SQLite (like 3.29.0 available in Qt 5.13.2 or a bit older), I've discovered that database now has a few more `sqlite_autodinex_*` entries in `sqlite_master` table (checked by test suite). I was puzzled at the beginning, but after some fiddling I see that creating this table: ``` CREATE TABLE "equipment_type"( id INTEGER NOT NULL PRIMARY KEY UNIQUE, name TEXT NOT NULL UNIQUE ) WITHOUT ROWID; ``` with recent SQLite, we get this set (only one) of internal indexes: ``` SELECT * from sqlite_master WHERE name LIKE 'sqlite_autoindex_eq%' index sqlite_autoindex_equipment_type_2 equipment_type 3 ``` Meanwhile, if I create same table using older 3.16.2 (on Debian 9 stretch), I get indexes for two fields: ``` index|sqlite_autoindex_equipment_type_1|equipment_type|3| index|sqlite_autoindex_equipment_type_2|equipment_type|4| ``` It feels strange if UNIQUE and PRIMARY KEY did not provoke creating `sqlite_autoindex`, but maybe we are missing something? Is this behavior change expected? Thanks! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Specific sqlite_autoindex_* missing in recent SQLite versions
Hi list, Accidentally, when performing VACUUM using rather old SQLite 3.16.2 (from Debian 8 stretch) binary on some database file created with more recent SQLite (like 3.29.0 available in Qt 5.13.2 or a bit older), I've discovered that database now has a few more `sqlite_autodinex_*` entries in `sqlite_master` table (checked by test suite). I was puzzled at the beginning, but after some fiddling I see that creating this table: ``` CREATE TABLE "equipment_type"( id INTEGER NOT NULL PRIMARY KEY UNIQUE, name TEXT NOT NULL UNIQUE ) WITHOUT ROWID; ``` with recent SQLite, we get this set (only one) of internal indexes: ``` SELECT * from sqlite_master WHERE name LIKE 'sqlite_autoindex_eq%' index sqlite_autoindex_equipment_type_2 equipment_type 3 ``` Meanwhile, if I create same table using older 3.16.2 (on Debian 9 stretch), I get indexes for two fields: ``` index|sqlite_autoindex_equipment_type_1|equipment_type|3| index|sqlite_autoindex_equipment_type_2|equipment_type|4| ``` It feels strange if UNIQUE and PRIMARY KEY did not provoke creating `sqlite_autoindex`, but maybe we are missing something? Is this behavior change expected? Thanks! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
